Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help to aggregate an expression with if statement

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

1 Reply
Anonymous
Not applicable
Author

Hi,

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

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