Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to find the month that had the highest amount of sales. So I want to go through each month for each year and find out which year and month had the highest amount of sales and how much.
Thanks for replying.
-Maulik
Ah, OK. Just remove the "total"s from my expression above, then.
=max({1} aggr(sum({1} [Sales Amount]),[Month],[Year]))
Ok. So I managed to get it work this way.
=max(aggr(sum([Sales Amount]), [Month], [Year]))
However, this will iterate over years based on the selection of the year(s). I want to ignore the selection. Can I do that?
Would using {1} or TOTAL help for all years/months?
I am not sure how to use them.
I tried {1} with max and sum like below and it worked. No matter what year I select, I always get last year from this code.
=max({1} [Year])
Still I can't seem to find a way to do what I want. Is it possible to use {1} in aggr function?
I am not sure how to use "total". I can't find its usage in help.
It may be worth looking into Set Analysis to achieve this ... this may allow you to isolate the data.
Mark wrote:Would using {1} or TOTAL help for all years/months?
maulik13 wrote:I am not sure how to use them.
Does this work?
=max({1} total aggr(sum({1} total [Sales Amount]),[Month],[Year]))
You can use the set analysis with set modifiers and it will work for all the months and years, irrespective of selections.
=max(aggr(sum({<[Year]=" ",[Month]=" ">}[Sales Amount],[Month],[Year])
Unfortunately above suggestions are not working. Selection changes the values. However I have got better understanding of Set Analysis and modifiers. Thank you all for that.
I have attached a sample qlik view file. What I need to do is to find the amount that is highest for any month for any year. I want this value to be Max limit for my bar chart. So that I can compare months better visually. Right now if I change the year the limit changes and bars don't show same scale for the dimension.
I hope I am clear.
Ah, OK. Just remove the "total"s from my expression above, then.
=max({1} aggr(sum({1} [Sales Amount]),[Month],[Year]))
Thanks. That is working exactly like I wanted.
So, {1} modifier in Sum gives me all the Sales for each month for each year regardless of selection.
And if I do like this
=max(aggr(sum( {1}[Sales Amount]),[Month],[Year]))
This gets affected by selection because of max aggregation on top. So I need to add {1} again in max in order to consider all the records and not just the selection.
=max({1} aggr(sum( {1}[Sales Amount]),[Month],[Year]))
Do I understand this right?