Solved

Top 10 Customers This Year vs. Last Year

  • 22 November 2023
  • 8 replies
  • 122 views
L
Rank

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.

icon

Best answer by lwang13 27 November 2023, 15:59

View original

8 replies

miguel80
Rank
Userlevel 5
Badge +1

Hi,

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

Miguel Q
K
Rank
Userlevel 2
Badge

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.

K
Rank
Userlevel 2
Badge

@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).

 

L
Rank

@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!

L
Rank

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!

L
Rank

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.

 

K
Rank
Userlevel 2
Badge

@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.

L
Rank

@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


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved