3 Replies Latest reply: Apr 28, 2016 10:11 AM by guohao yee RSS

    Using Filter Pane to change time dimension on Charts

    guohao yee

      Hey guys,

      I am trying to work on the time filter where the user can change the time dimension displayed on charts.

       

      Ideally, there will be a filter pane of a field (DisplayPeriod) at the top of the page with two choices – YTD and Rolling 13 month.

      The default display period should also be set as the YTD option.

       

      “DisplayPeriod” will be an additional calculated column added to the master calendar based on the date.

      Then, I am trying to create a master dimension that consist of a calculated time frame based on the “DisplayPeriod” field.

       

      However, I haven’t had much success so far.

       

      Please let me know if you have any ideas or advice!

       

      Thanks in advance!

        • Re: Using Filter Pane to change time dimension on Charts
          guohao yee

          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:


          Wildmatch(Display,'YTD','Rolling13Month')


          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!

            • Re: Using Filter Pane to change time dimension on Charts
              Vladimir Komarov

              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:

              Screen Shot 2016-04-27 at 9.38.32 AM.png

              But it might work for you and will definitely save you some time developing something similar on your own.

               

              Regards,

              Vladimir

                • Re: Using Filter Pane to change time dimension on Charts
                  guohao yee

                  Hi Vlad,

                   

                  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)

                  This expression,

                  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.