Month: December 2015

QlikView Previous and Next Selections

~ pramaraj0459 ~ Leave a comment

Recently I got a use case to use this feature Previous and Next selections of QlikView effectively.

Use case: 

“What if? Analysis” at both summary level and detailed level of charts. Due to some reasons unable to use QV group feature so created customized drill down by showing and hiding charts. The detailed chart will be shown upon selecting particular Reporting Date on the summary chart and this chart pops up with a set of sliders so the user can do “What if?”. The catch here when the user clears the Reporting date selection and goes back to Summary chart still he should be able to see the forecast for that particular chart unless he clears them completely.

Ok, now some basics about Previous/Forward selections.

{$N} – Previous Selection

{$_N} – Forward Selection

N is an integer.

Note: Till integer 5 there won’t be any issue, but after that you can see error highlighting. Initially, I thought it’ll work only for 5 previous and next selections that are wrong it can support more, I tested up to 50 previous selections.

Previous Selections

Solution:

I thought below expression would do the trick, but it didn’t.

only({$1} Reporting_Date)

I was not getting consistent result, meaning sometime it returned null value as well. Problem could be due to the triggers in my QVW. So I came up with more robust logical expression. Basically it checks whether first previous selection {$1} has any selection if not it goes to next level and check, does the same for five levels {$5} this was solidly sufficient to meet my requirement.

=if(isnull(only({$1} [Reporting_Date])), if(isnull(only({$2} [Reporting_Date])), if(isnull(only({$3} [Reporting_Date])),if(isnull(only({$4} [Reporting_Date])), only({$5} [Reporting_Date]), only({$4} [Reporting_Date])),only({$3} [Reporting_Date])) ,only({$2} [Reporting_Date)), only({$1} [Reporting_Date]))

.. stored above expression in a variable and used it in appropriate places.

What If Analysis.

Spike in the image is forecasted value.

Note: Give option for user to clear the previous selections otherwise he might feel like locked up with something. I did it by creating a button and activating the trigger “Clear All” for five times.