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
since your rows seams to be unique, you could also use concat, sum etc…
This is cool!
Why can’t we use interval function which calculates the difference between any two dates??
@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.