max yearmonth fixed not based on selections

[#1]

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

[syed_muzammil]

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.

[#3]

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

[#4]

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....