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