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]

Correct Maulik13 🙂

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

[#3]

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.

[johnw]

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.

[#5]

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