Monday, January 30, 2012

Conditional Dimensions and Expressions – Tips and Tricks



With the shipment of QlikView 11 comes an incredibly cool feature:  Conditional Enablement of Dimensions and Expressions.  That is “fancy speak” for being able to change the columns in your chart based on any number of conditions.

This can have various purposes but the one I am using most often is the ability for your users to create their own Ad Hoc reports.  Let’s create an example to illustrate some tips I have learned.

You can find the example qvw here.

The first thing you will want is an inline table of the dimensions and expressions you will want the user to be able to select.  I would recommend two columns, one for the descriptor and one to uniquely identify it by a single letter. 

AdHoc_Dimensions:
LOAD * INLINE [
    _Dimension, _Dim_ID
    Order Num, A
    Product, B
    Class, C
    Color, D
    Year, E
    Month, F
]
;

This table will not be associated to any other tables in your model.  Depending on your need, you might create a table for your selectable dimensions and one for your selectable expressions.


Now that you have your selectable dimensions and/or expressions to choose from, you can go to the layout and create list boxes to show the options.  I like to add the Clear and Select All menu icons to the captions for these lists.  It might also help to add a help message or label the caption with “Pick the columns to display” or similar.



Now create the object that the user will be customizing.  You can put conditional dimensions or expressions in any kind of object.  Here, we will create a straight table.  Add all the fields referenced by your dimension list.  And add the calculated expressions that relate to any selectable expressions you created. 
This is where the magic happens.  In the dimension properties, check the option for “Enable Conditional” and enter the following code:

=SubStringCount(Concat(_Dim_ID, '|'), 'A')

You will want to add similar code to each dimension, changing the letter to correspond with what you used in your inline table.  Then do the same thing for your expressions if desired.
The last thing to do is add a Calculation Condition to the general tab of the object so that the object only displays when at least one dimension and expression is selected:

GetSelectedCount(_Dimension)>0 AND GetSelectedCount(_Expression)>0

I then add a message to Error Messages for “Calculation condition unfulfilled” that reads similar to “Please choose at least one dimension and one expression to display... “

Click OK and now you should have a working Ad Hoc report.  Choose various dimensions and expressions and the chart should change according to your selections.


Some tips and warnings:
  • I use a letter ID because there is less potential of accidently displaying more dimensions than intended with the SubStringCount function.  For example, 1 will return a result for 1, 10, 11, 21, 100 etc.  Using the actual dimension names can also be troublesome especially with columns like “Invoice” and “Invoice Line” for example.
  • As of this writing (QV11 Initial Release), there is a bug in interactive sort for columns.  I haven’t been able to isolate the behavior.
  • Also, I have found that when using a pivot table, there is sometimes an issue when attempting to pivot a dimension horizontally.






Even with the pivot and sortation issue, I am finding that my clients are very happy with this new flexibility.  It was something not easily accomplished in prior versions.

I hope you have success with this and are able to expand the uses further.

12 comments:

  1. Have you had much luck with this option? We get an 'allocated memory error' quite often even with small datasets when using this approach. Thanks.

    ReplyDelete
  2. I use the approach regularly on data sets in the tens of millions with no issue. There might be a better way to accomplish this, but this method is an improvement (IMO) on the method used in the What's New in QV11 demo (located on QlikView site)

    ReplyDelete
  3. Have you had any luck with resolving your 3rd issue?
    --Also, I have found that when using a pivot table, there is sometimes an issue when attempting to pivot a dimension horizontally.

    ReplyDelete
  4. I am trying to use it in a slightly modified version of this and am stuck, could you please help?

    I basically have a regional hierarchy (Country, State and City - as three columns) and want a chart in which the dimension changes automatically depending on what is selected and it shows a level lower than that.
    So if Country, state and city are Level 1, 2 and 3 respectively and one selects a country in level 1 - the chart should have Level 2 (states within the city) as the dimension. If a state is selected, it should change dimension to a level 3 (cities within the selected state(s)). With no selections, i want level 1 (country).

    I basically want a condition/if statement that'd look at the selections in Level 1/2/3 and make appropriate (a level deeper) selection in the list box (equivalent of "pick your dims"). Don't know how to do it though :|

    ReplyDelete
  5. You want to look at drill-down groups. They behave exactly like what you are looking for.

    ReplyDelete
  6. Awesome! That worked. Can i put a drill down group as a dimension in a chart like the way you described here? So instead of having class, color, month, year, etc. as dimensions - i put class, color, TIME (a drill down group of year and month), etc as dimensions, possible?

    ReplyDelete
  7. Hi
    I tried your little trick and it works fine on the fields that I'm not renaming when I load my table. However fields I'm renaming doesn't seem to work.

    F.ex. in my script I'm renaming this:
    ...
    over_studretn as Uddannelse,
    ...

    and in my load inline I have this:

    AdHoc_Dimensions:
    LOAD * INLINE [
    _Dimension, _Dim_ID
    Uddannelse, A
    Fakultet, B
    Campus, C
    ];

    Fakultet and Campus works fine, but not Uddannelse. Is there an easy explanation?

    Cheers
    Bruno

    ReplyDelete
  8. Hey! Thanks for sharing this tip. It worked for me.

    ReplyDelete
  9. Thank you, if you developed more that technique I'd like to know more about it :)

    ReplyDelete
  10. Hi,
    is there a way to limit the expressions shown if there are no selections done in the _Expression ? I have multiple listboxes using this technique, and thus if there are no selection in one of them, all expressions are shown. I did a work around with a value that is called "Empty" with an ID that is not related to anything. It works, but it's ugly!!

    ReplyDelete
  11. Simon,
    You need to create a calculation condition in the chart so that when nothing is selected either in the dimension list or the expression list, the chart will not render.
    I use something like this:
    GetSelectedCount(_Dimension)>0 AND GetSelectedCount(_Expression)>0

    ReplyDelete