Include MaxString in a formula

Mon, 07 Sep 2009 11:22:31 GMT

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

Include MaxString in a formula

Anonymous — Mon, 07 Sep 2009 12:59:55 GMT

Set Analysis is your friend here:

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

This should do the trick.

//Jsn

Include MaxString in a formula

Mon, 07 Sep 2009 13:08:37 GMT

Hi Johannes,

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

Thanks

David

topic Include MaxString in a formula in QlikView

Include MaxString in a formula

Include MaxString in a formula

Include MaxString in a formula