Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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 - Contributor
Partner - Contributor

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