Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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.

Thanks for replying.

-Maulik

1 Solution

Accepted Solutions
MVP
MVP

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

13 Replies
Not applicable

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?

Not applicable

How to iterate over each month for each year?

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

Not applicable

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.

Not applicable

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.

MVP
MVP

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

Not applicable

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

Not applicable

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.

MVP
MVP

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

Not applicable

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?