topic max yearmonth fixed not based on selections in QlikView

max yearmonth fixed not based on selections

Thu, 30 Dec 2010 13:38:50 GMT

Hello!

I would like to base my expression on the max yearmonth. Suppose my max yearmonth is 2010 09 in application. Even when I select 2010 05 for example, I would like the expression is based on 2010 09. It is a fixed sheet, I realise that. Is this possible?

My expression at te moment is:

Sum({$< Yearmonth = {$(=max(vYearMonth))},Yearnumber=,Yearmonth=>} Score)

vYearMonth is a variable: =Max(Yearmonth)

So what I would like is this expression not based on selections. How can I change the expression and/or variable so that it is always based on max yearmonth in application?

And...do I need the vYearMonth variable?

Thank you in advance!

Best regards,

Patricia

max yearmonth fixed not based on selections

syed_muzammil — Thu, 30 Dec 2010 13:48:33 GMT

Hi,

At the script level order your table( in which you have your Year and Month) by Year and Month in descending or ascending. Then create a variable

vMax=Peek(YearMonth,0) if descending.

vMax=peek(YearMonth,-1) if ascending.

Use this variable in your expression.

max yearmonth fixed not based on selections

Thu, 30 Dec 2010 16:29:56 GMT

Hi Patricia, you actually do not need the variable. You can use a similar expression to the following:

Sum({$< Yearmonth = {$(=max({1} YearMonth))},Yearnumber=,Yearmonth=>} Score)

Notice the set analysis within the max funtion; that will force QlikView to evaluate the maximum yearmonth through the whole document. Also, make sure to clean all dates indirect selections, so this approach can work.

Regards

max yearmonth fixed not based on selections

Mon, 10 Jan 2011 12:36:06 GMT

Thanks both for your reply.

Problem with the peek....is that I try to build in an incremental load. Each month I load the new month seperately. I think this won't work.

Solution of Ivan is only working when I include all Calendar options (bookyear code, number, month, quarter, etc.) in the formule (just like Yearnumber and Yearmonth). So, I did, I think it is working now. But sometimes I get the memory error....