How to iterate over each month for each year?

[#1]

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

[#2]

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?

[#3]

Would using {1} or TOTAL help for all years/months?

[#4]

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.

[#5]

It may be worth looking into Set Analysis to achieve this ... this may allow you to isolate the data.

[johnw]

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

[#7]

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

[#8]

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.

[johnw · Accepted solution]

Ah, OK. Just remove the "total"s from my expression above, then.

=max({1} aggr(sum({1} [Sales Amount]),[Month],[Year]))

[#10]

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?