Sales amount based on sno and invoice

[Rajkumar]

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

[sebastiandperei]

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])

[Rajkumar]

Thank you for reply

if want to show it in KPI.How would I show.

[sebastiandperei]

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.