Waterfall Chart - Measure is time gap between two fields

[gitguto]

Hello everyone, I need to create something like this:

My dimension would have to be an aggregated function, something like:

=aggr(if(
only({<name = {'Create proposal'}>}activityfinishdate)-only({<name = {'Study approval'}>}activitystartdate)<16,
'0 - 15 days',if(
only({<name = {'Create proposal'}>}activityfinishdate)-only({<name = {'Study approval'}>}activitystartdate)<31,
'15 - 30 days','30+ days')),[Request Number])

I'm creating slots according to the difference between the finish date of the task "Create Proposal" and the start date of the task "Study Approval". That is, "0-15 days", "15-30 days" and "+30days"

But I'm having trouble getting this visualization, the closest I can get is this:

Any help would be very appreciated. Thanks

[jbhappysocks · Accepted solution]

Hi!

If you want to create a Qlik Waterfall chart you don't add a dimension, you just create the bars. 

Your first bar could be something like this: 

sum(aggr(if( only({<name = {'Create proposal'}>}activityfinishdate)-only({<name = {'Study approval'}>}activitystartdate)<16,1,0),[Request Number]))

(if it is no of Request Number you are after and they are distinct, I had no example data to work with)

Second bar:

sum(aggr(if( only({<name = {'Create proposal'}>}activityfinishdate)-only({<name = {'Study approval'}>}activitystartdate)<31,1,0),[Request Number]))

-

sum(aggr(if( only({<name = {'Create proposal'}>}activityfinishdate)-only({<name = {'Study approval'}>}activitystartdate)<16,1,0),[Request Number]))

and last bar:

sum(aggr(if( only({<name = {'Create proposal'}>}activityfinishdate)-only({<name = {'Study approval'}>}activitystartdate)>30,1,0),[Request Number]))

Use the checkbox Add subtotal in the second and third bar to add the total bars.

The calculations can probably be less complex than my examples from your function, (but if I understood you correct I think it should work). The key take away is you don't add dimensions in a waterfall chart, you only add measures and specify their relationships.

[gitguto]

Thank you very much! Your answer was perfect. Helped me a lot!