Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
Sample data:
SERIAL NO | INVOICE ID | DATE | SALES AMOUNT |
1 | CX9484 | 01-Jan-19 | 865 |
2 | CX9484 | 01-Mar-19 | 765 |
1 | CX9485 | 01-Oct-19 | 658 |
2 | CX9485 | 01-Feb-19 | 345 |
1 | CX5863 | 01-Mar-19 | 987 |
3 | CX5863 | 01-Jun-19 | 567 |
1 | CX9484 | 01-Apr-19 | 345 |
Output:
based on latest s.no the invoice has to sum up sales value
sample output:
SERIAL NO | INVOICE ID | DATE | SALES AMOUNT |
2 | CX9484 | 01-Mar-19 | 765 |
2 | CX9485 | 01-Feb-19 | 345 |
3 | CX5863 | 01-Jun-19 | 567 |
Hi!
If you have only one value for each SERIAL NO, INVOICE ID and DATE:
INVOICE ID as DIMMENSION
SALES AMOUNT EXPRESSION: FirstSortedValue([SALES AMOUNT], -[SERIAL NO])
DATE EXPRESSION: FirstSortedValue(DATE, -[SERIAL NO])
SERIAL NO EXPRESSION: MAX([SERIAL NO])
Thank you for reply
if want to show it in KPI.How would I show.
But.... which value do you want to show in one kpi? in your example you have one value for each invoice....
If you want to see the sum of the amount:
sum(aggr(FirstSortedValue([SALES AMOUNT], -[SERIAL NO]),[INVOICE ID]))
Note: using aggr will be affected by the dimmensions you use. Is is the case for show in a KPI.