Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.