Skip to main content

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.

Hi,

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

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.

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

 

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

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!

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.

 

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

@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