I have created a table with the field "Display" with 2 values - 'YTD' and 'Rolling 13 month'.
Then I create a Dimensions under the Master items "DisplayPeriod" using the following expression:
If( Len( Wildmatch( Display, 'YTD', 'Rolling13Month' ) ) = 0, YTD,
If( Wildmatch( Display, 'YTD', 'Rolling13Month' ) = 1, YTD,
If( Wildmatch( Display, 'YTD', 'Rolling13Month' ) = 2, Rolling13Month, YTD
I inserted a Filter Pane that contains the field "Display" into my app and a KPI box that has the measure:
When the user select "YTD", the Wildmatch KPI box returns 1 and the chart that uses the dimension "DisplayPeriod" now display the YTD Period.
Similarly, when the user select "Rolling13Month", the Wildmatch KPI box returns 2 and the chart that uses the dimension "DisplayPeriod" now display the Rolling 13 Month Period.
However, when no value were selected, the Wildmatch KPI box returns a '-' but my chart still display the Rolling 13 Month Period.
I tried using IsNull which return -1 (True) and Len which return 0 in my first if condition but the "DisplayPeriod" still returned the Rolling 13 Month Period.
Any help will be very much appreciated!
There is a new "feature" called Alternative Dimensions, introduced in QS 2.2.
It allows you (or the app's user) to switch dimensions on the fly.
The UI for activating/using this feature is not perfect, IMHO:
But it might work for you and will definitely save you some time developing something similar on your own.
Thanks for your suggestion.
I was using Qlik Sense 2.1 so I didn't see that feature.
I tried it on a v2.2 but I find that it isn't very intuitive for the user to click on the settings to explore that option on their own.
I found a workaround to my issue. The main problem is that when the function
Wildmatch( Display, 'YTD', 'Rolling13Month' ) is applied, a dimension of values is returned since "Display" was a dimension field.
i.e. Both the value 1 and 2 will be returned as a dimension.
If i create a dimension X with the expression Wildmatch( Display, 'YTD', 'Rolling13Month' ) under the master item,
Then, X will be a field with values 1 and 2..
Hence, the IF function will return both "YTD" and "Rolling13Month".
i.e. if I create a table with "Display" and "Display Period" as dimension, I will see months from the "YTD" field and "Rolling13Month" field. Since they overlaps, I always thought that I am seeing only "Rolling13Montth".
In order to get only "YTD", I need the expression to return only one value.
Thus, I used the Min function with TOTAL together with MixMatch to get the lowest value. The full expression is
If(Min( TOTAL Mixmatch(Display,'YTD','Rolling13Month')) = 1,YTD,Rolling13Month)
Min( TOTAL Mixmatch(Display,'YTD','Rolling13Month'))
will return the value 1 when no selection on "Display" is made and when "YTD" is chosen since "YTD" will be in the field "Display" in these scenarios.
When "Rolling13Month" is chosen, the expression will return 2 and the "DisplayPeriod" field will only contain the values from the "Rolling13Month" field.
This use of the Master Dimension is very useful if the user would like to see a different time period by just simply clicking on a selection in the filter pane.
On a side note, the implementation would be much easier if the default period is the longer period (i.e. Rolling13Month in my case) as a set analysis would have solved the problem.