# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for
Did you mean: Not applicable

## Include MaxString in a formula

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

1 Solution

Accepted Solutions Anonymous
Not applicable
Author

Set Analysis is your friend here:

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

This should do the trick.

//Jsn

2 Replies Anonymous
Not applicable
Author

Set Analysis is your friend here:

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

This should do the trick.

//Jsn Not applicable
Author

Hi Johannes,

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

Thanks

David Community Browser