Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Rajkumar
Creator
Creator

Sales amount based on sno and invoice

Hi Everyone,

Sample data:

SERIAL NOINVOICE IDDATESALES AMOUNT
1CX948401-Jan-19865
2CX948401-Mar-19765
1CX948501-Oct-19658
2CX948501-Feb-19345
1CX586301-Mar-19987
3CX586301-Jun-19567
1CX948401-Apr-19345

 

Output:

based on latest s.no the invoice has to sum up sales value

sample output:

SERIAL NOINVOICE IDDATESALES AMOUNT
2CX948401-Mar-19765
2CX948501-Feb-19345
3CX586301-Jun-19567

 

3 Replies
sebastiandperei
Specialist
Specialist

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
Creator
Creator
Author

Thank you for reply

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

sebastiandperei
Specialist
Specialist

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.