Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Refer to a date dimension in chart expression

I have a chart with one dimension reflecting unique dates of product completion from client population (client can have multiple products with different completion dates). I then need to calculate how many clients will fall into each date bucket, which are based on the max completion date for a client.

I came up with this formula, and it works as long as I hardcode the date:

sum(if(aggr(max([Completion Date]),Client) < '31/12/2017',1,0))

... but it doesn't work if instead of hardcoding the date, I try to refer to the date in the dimension, which is what I need to in order to make this dynamic. I tried 'only', putting the date in a different column and referring to it as column(1), I tried creating a different column to hold the date, I tried pick formula and feeding it with dates... nothing worked.

The only thing that worked is creating multiple expressions, one per month, like:

sum(if(aggr(max([Completion Date]),Client) < '31/10/2017',1,0))

sum(if(aggr(max([Completion Date]),Client) < '30/11/2017',1,0))

sum(if(aggr(max([Completion Date]),Client) < '31/12/2017',1,0))

...

but that's not scaleble...