Include MaxString in a formula

[#1]

Hi,

I have a variable called MonthNum which is the number of the month in a financial year, therefore July = 1, August = 2 etc.

I want to be able to look at the average of a set of data as well as the current months data side by side.

To do this I have a formula to work out the average as:

Avg(if(Year = vCurrentYear, Sales))

When a range of months is selected this gives the average but at the same time I want a formula that will display the most recent month sales in the month range I select. So if I choose MonthNum 1 to 8 then the average gives me an average of 8 months data and the current month gives me the data for MonthNum = 8

I could do it like this:

Sum(if(Year=vCurrentYear and MonthNum = 8, Sales) )

but then its stuck on month 8 until I change the formula.

If I add another expression and use

MaxString(MonthNum)

I get 8 as the answer, so I want to be able to add these together but I cannot nest a MaxString into a Sum function.

This doesnt work

Sum(if(Year = vCurrentYear and MonthNum = MaxString(MonthNum), Sales))

Any ideas?

David

[Anonymous · Accepted solution]

Set Analysis is your friend here:

sum({$<Year={$(=vCurrentYear)}, Month={$(=Maxstring(Month))}>} Sales)

This should do the trick.

//Jsn

[#3]

Hi Johannes,

worked perfectly, Im guessing this is using set analysis which I never understood but can see what this is doing.

Thanks

David