Skip to main content
Solved

Top 10 Customers This Year vs. Last Year


Forum|alt.badge.img

I am working on creating a dashboard chart to show top 10 customers we billed this year vs. last year. The underlying GI is simply using the ARInvoice table to pull out all invoices with positive number and credit memos with negative numbers without grouping. I then added a new column using the Year () formula with DocDate to get the Year field. However, when I put the “year” as series, I got error. Can someone help me with it?

I also tried to link the ARInvoice table with MasterFinPeriod table to use FinYear field, but I got the same error for the dashboard chart.

Best answer by lwang13

khalmehcgc wrote:

@lwang13  So, it looks like the aggregation does handle the Blank values, so if you add a filter similar to the one shown below it should work for you (I used the Ext.Amount column).

 

Thanks @khalmehcgc 

“Is Not Empty” doesn’t work, but I tried the Amount “Does Not Equal” to 0 and it worked!

View original
Did this topic help you find an answer to your question?

8 replies

miguel80
Semi-Pro I
Forum|alt.badge.img+1
  • Semi-Pro I
  • 68 replies
  • November 23, 2023

Hi,

I think that perhaps you’re doing it backwards. I mean, shouldn’t the year be the category and the customer the series?


Forum|alt.badge.img
  • Jr Varsity II
  • 15 replies
  • November 23, 2023

You should be able to have the year & customer be either way depending on how you want to see the chart. I tested both and got the same error. I was able to aggregate with a count and get it to work, but the SUM produces the error. I’ll look into it further.


Forum|alt.badge.img
  • Jr Varsity II
  • 15 replies
  • November 23, 2023

@lwang13  So, it looks like the aggregation does handle the Blank values, so if you add a filter similar to the one shown below it should work for you (I used the Ext.Amount column).

 


Forum|alt.badge.img
  • Author
  • Jr Varsity II
  • 15 replies
  • Answer
  • November 27, 2023
khalmehcgc wrote:

@lwang13  So, it looks like the aggregation does handle the Blank values, so if you add a filter similar to the one shown below it should work for you (I used the Ext.Amount column).

 

Thanks @khalmehcgc 

“Is Not Empty” doesn’t work, but I tried the Amount “Does Not Equal” to 0 and it worked!


Forum|alt.badge.img
  • Author
  • Jr Varsity II
  • 15 replies
  • November 27, 2023
miguel80 wrote:

Hi,

I think that perhaps you’re doing it backwards. I mean, shouldn’t the year be the category and the customer the series?

Hi @miguel80 thank you for replying. I tried backwards and it didn’t work. However, I tried to add the condition that the Amount Doesn’t Equal to 0 and it worked!


Forum|alt.badge.img
  • Author
  • Jr Varsity II
  • 15 replies
  • November 27, 2023

Hi @khalmehcgc , do you know how to get the result sorted by this year’s amount? I got it work but the sorting looks bad. 

 I can only set it sorted by field value, but couldn’t figure out a way to define which year’s amount.

 


Forum|alt.badge.img
  • Jr Varsity II
  • 15 replies
  • November 28, 2023

@lwang13 It appears to me that it’s sorting on the sum of the amounts from both years. I’m not sure if it’s possible to get it to sort on an individual year. Possibly posting a new question would help since this one is marked solved.


Forum|alt.badge.img
  • Author
  • Jr Varsity II
  • 15 replies
  • December 1, 2023
khalmehcgc wrote:

@lwang13 It appears to me that it’s sorting on the sum of the amounts from both years. I’m not sure if it’s possible to get it to sort on an individual year. Possibly posting a new question would help since this one is marked solved.

Thank you @khalmehcgc for checking. I will post a new question.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings