Calculating a Duration Using Two Rows

I’m always tickled to learn something new in QV, especially when it’s simple and elegant. swuehl, a prolific and respected contributor to QlikCommunity, answered this Community post with a brilliant solution.

The problem was how to calculate a Project (the Dimension) duration using dates from two different rows in the source table. I would have automatically reached for a script solution, Generic Load. But what if you don’t want to modify the script or don’t want the flattened model produced by Generic Load?

Swuehl’s solution computed the value in the chart using Set Analysis to fetch the needed values from the appropriate row.

only({<progress = {deployed}>} entry_date ) 
   – only({<progress = {started}>} entry_date)

The cleverness lies in using the only() aggregation function. Set Analysis can only be used in aggregation functions, so he used only() to enable specifying the Set.

I like it.

-Rob

Share

4 thoughts on “Calculating a Duration Using Two Rows”

  1. @Abhinava

    The QV Interval function formats an interval value for display, it does not calculate anything. The results of the above calc could be wrapped in an interval() for display.

Comments are closed.