topic max graph date from variable period in QlikView

max graph date from variable period

Thu, 02 Sep 2010 15:48:21 GMT

hi is there a way to use a variable date as a max period on a graph ?

e.g. my expression below gives the 'Final Total' for months where the max Month is the variable [vReportDate]

sum([vReportDate]=max(Month),GP_Wait_List_Plan)

But this doesnt work.

Any ideas?

max graph date from variable period

vgutkovsky — Fri, 03 Sep 2010 06:57:21 GMT

Well your syntax won't work because you have a nested aggregation, plus it's not proper set analysis syntax. But something like this could work:


sum({<Month={"<=$(vReportDate)"}>}GP_Wait_List_Plan)

Regards,

max graph date from variable period

Fri, 03 Sep 2010 09:41:06 GMT

Hi Vlad, thnks for your help the expression worked a treat.

Im just wondering if you can also help with the following in regards to the expression.

The expression works fine but it causes an issue when the user selects a period form the 'Month / Year' list box.

Basically although i want my expression to provide a maximum period for Actual BUT if the user selects a period which falls before the maximum then it should use the earlier period.

E.g. the user has selected April - 09 - Jul -2009 from the list box. Therefore the graph maximum date should be Jul - 09. As you can see the expression has made the graph show max date Jul - 10 (data points in Red Box shouldn't be shown).

Maybe set analysis should be included in the dimension?

Any ideas?

max graph date from variable period

vgutkovsky — Fri, 03 Sep 2010 16:52:50 GMT

I would recommend that you change this not in the expression, but in the variable. So put in something like this for the variable value:


=rangemin(
 date#('07/2009','MM/YYYY'),
 if(len(trim(GetFieldSelections(MonthYear)))>0,date(max(MonthYear)))
)

where 07/2009 is replaced by your manual maximum and MonthYear is replaced by the user date selection field.

Regards,