13 Replies Latest reply: Jan 22, 2010 9:14 AM by Maulik Kataria

# How to iterate over each month for each year?

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.

-Maulik

• ###### How to iterate over each month for each 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?

• ###### How to iterate over each month for each year?

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

• ###### How to iterate over each month for each year?

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.

• ###### How to iterate over each month for each year?

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

• ###### How to iterate over each month for each year?

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

• ###### How to iterate over each month for each 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])

• ###### How to iterate over each month for each 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.

• ###### How to iterate over each month for each year?

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

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

• ###### How to iterate over each month for each 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?

• ###### How to iterate over each month for each year?

Correct Maulik13 :)

You need a {1} for the Sum expression part and one for the Max expression part.

• ###### How to iterate over each month for each year?

Just to add as I understood more...

Removing total helped because total will ignore the dimension and iterate over all records in the table and this gets affected by selection. So removing total and only using {1} will iterate over fields specified in aggr function.

• ###### How to iterate over each month for each year?

Yeah, "total" says to calculate the same value for each combination of dimensions, so it's essentially ignoring the dimensions. That pretty much cancels out the whole purpose of using aggr(), so it should have been obvious to me that my original expression was wrong. Ah, well. At least we got the right one with a little fiddling around.

So removing "total" and only using "{1}" iterates over the fields specified in the aggr function while ignoring all selections that have been made.

• ###### How to iterate over each month for each year?

Thank you for clarifying that in detail. It helped me a lot in understanding set analysis.