Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Mehtab
Contributor
Contributor

sum of sales on the max available date of each month

I am trying to find out the sum of sales on the max available date of each month.

i am using the below set analysis:

SUM({<[Date]= {"=Aggr(max([Date]),[Month])"}>}[Sales])

the above expression returns the sum of sales on the first day of each month

even though Aggr(max([Date]),[Month]) returns the correct max available date of each month.

Please help

 

 

Labels (1)
15 Replies
pradosh_thakur
Master II
Master II

If you have month as a dimension the below shall work

SUM({<[Date]= {"$(=max([Date]))"}>}[Sales])
Learning never stops.
Mehtab
Contributor
Contributor
Author

It gives the sum sales on the max date of the max month.

However i need the sum of sales on the max date of all the month.

I need to make a master measure like that .

Hence when put against any dimension it should give the sum of sales on the max date of each month for that dimension

pradosh_thakur
Master II
Master II

as i said if you have month as a dimension the above expression shall work.

Learning never stops.
Mehtab
Contributor
Contributor
Author

It is not. It is only giving the sum of sales for the latest month.
ferryjong
Partner
Partner

Hi,

Maybe using the Dollar Sign Expansion in your Set Analysis will help, like:

SUM({<[Date]= {"$(=Aggr(max([Date]),[Month]))"}>}[Sales])

pradosh_thakur
Master II
Master II

My bad. I didn't realise what you wanted. Set analysis is calculated once per chart so taht's obvious.

your expression shall work, i tried it.

 

What dimension are you using? I tried with similar sample data and it's working for me.

Learning never stops.
Mehtab
Contributor
Contributor
Author

I get no data using dollar sign in this expression
Mehtab
Contributor
Contributor
Author

I am using month as the dimension
Mehtab
Contributor
Contributor
Author

when i am using the below expression
FirstSortedValue(Aggr(sum([Sales]), [Date]), -[Date])
i am getting correct output but only for Month dimension when any other dimension is being used it gives wrong values