Help to aggregate an expression with if statement

Tue, 23 Aug 2016 09:56:26 GMT

n My application, I have a calendar based on the field named as Date.

When I select one date in calendar for example 19/07/2016, I do get correct value for my selected date in the expression below:

count({$<Pivot_Item_Type = {'SUBST'}, Z_Active_For_Ava = {1}>} Distinct aggr(if( sum({$<Date={">=$(=max(Date)-1)<$(=Date)"},Pivot_Item_Type = {'SUBST'}, Z_Active_For_Ava = {1}>}QTY_STOCK) = 0,POC_CODE,null()), POC_CODE, SUBSTITUTION_ITEM))

My next requirement is that if I have selected the calendar = 19/07/2016, I need to build a bar chart which will show the value of the expressions starting 45 days ago that is Date - 45 till the selected Date = 19/07/2016.

That is Dimension of my bar chart will be Date.

Any idea how to do this?

I have tried the following but it does not work:

Expression in bar chart:

=Count({$<Date={">=$(=Date(max(Date)-45))<=$(=max(Date))"} >}
Aggr(
count({$<Pivot_Item_Type = {'SUBST'}, Z_Active_For_Ava = {1}, Date={">=$(=Date(max(Date)-45))<=$(=max(Date))"}>} Distinct aggr(if( sum({$<Date={">=$(=max(Date)-1) <$(=max(Date))"},Pivot_Item_Type = {'SUBST'}, Z_Active_For_Ava = {1}>}QTY_STOCK) = 0,POC_CODE,null()), POC_CODE, SUBSTITUTION_ITEM))
, Date)
)

Re: Help to aggregate an expression with if statement

Anonymous — Tue, 23 Aug 2016 15:46:15 GMT

Hi,

try to add Date to max(Date) always in your set analysis:

{$<Date={">=$(=Date(max(Date)-45))<=$(=DATE(max(Date)))"} >}

topic Help to aggregate an expression with if statement in QlikView

Help to aggregate an expression with if statement

Re: Help to aggregate an expression with if statement