Calculated Dimensions

Mon, 23 Jun 2014 12:40:13 GMT

I want to have a 'histogram' that works for selecting one single month, but also in case I select multiple (e.g. 2 months).

In this histogram on the X-axis I want to show the 'AppUsageFrequencyCategories': 1x, 2x, 3x, 4x, 5x, 6+x for nr of times a certain App has been used. This AppUsageFrequencyCategory is dependent on AppUsage attribute which contains how many time the App is used in a specific month for a specific country etc.

In the expression I calculate the number of devices (COUNT DISTINCT DeviceID) for which in a certain month the selected App (=ServiceID) has been 1x, 2x, 3x etc.

I first had the 'AppUsageFrequencyCategory' as a calculate attribute, but I do understand now I need a Calculated Dimension for it.

Simply example of the problem why my currently defined Calculated Dimension does not seem to work:

For Month 20140101:

DeviceID, AppUsage

7602767 2

7603047 2

8538589 9

For Month 20140201:

DeviceID, AppUsage

7602767 1

8102376 2

8538589 13

I get:

For Month 20140101 and 20140201 (both selected):

DeviceID, AppUsage

7602767 2

7603047 2

8102376 2

8538589 9

8538589 13

I need:

For Month 20140101 and 20140201 (both selected):

DeviceID, AppUsage

7602767 3 (1+2)

7603047 2

8102376 2

8538589 22 (9+13)

And because "I get" (see above) is not what "I need", the histogram will count the number of Unique Devices wrongly as it does a count distinct devices within the AppUsageFrequency Categories. I see that I have some double counting if in the first month the App Usage for a unique Device is in a different 'category' then in the second month etc.

My Calculated Dimension is the following:

=IF(AppUsage=1,'1x',IF(AppUsage=2,'2x',IF(AppUsage=3,'3x',IF(AppUsage=4,'4x',IF(AppUsage=5,'5x','6+')))))

The problem is that I need to do the SUM or AGGR over the selection (e.g. the two months) so that a DeviceID that uses that App e.g. 1x in Month 1 and 1x in Month 2 is calculated as '2x' and not as '1x'.

Before I had the following line as a calculated attribute in my table (which work if I select only one month):

IF(AppUsage=1,'1x',IF(AppUsage=2,'2x',IF(AppUsage=3,'3x',IF(AppUsage=4,'4x',IF(AppUsage=5,'5x','6+'))))) AS AppUsageFreqCategory

Any suggestion?

If needed I can provide the source code, or a picture of the current QV model I have.

P.S. I have already read "HIC" article about Calculated Dimensions.

topic Calculated Dimensions in QlikView

Calculated Dimensions