Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
educastri83
Creator
Creator

Calculated Dimension on a Pivot Chart

Hello,

I'm currently working on a dashboard. In the data model there is a dimension called "Category" with 4 values:

Category
Category 1
Category 2
Category 3
Category 4

I'm needing to build a pivot chart that in a single column or dimension shows the possible combinations of the same field "Category" and in the expression count the number of users that have granted permision in that combination. The expected result would be something like this:

Dimension                     Count (Distinct .........USERNAME)
Category 1 and Category 2                                                    2
Category 2 and Category 3                                                    4
Category 2 and Category 4                                                    2
..And so on                                         ...................

 

For example, the following expression returns the correct calculation to count the users that have the combination of category "Category 1" and "Category 2", but it is not functional for me since it does not have it in one dimension.

 

Count(DISTINCT {<USERNAME = {"=(COUNT(DISTINCT {<Category={'Category 1'}, Granted={'Y'}  >} USERNAME) > 0 
AND COUNT(DISTINCT {<Category={'Category 2'}, Granted={'Y'} >} USERNAME) > 0) "}>} USERNAME)

 

In the example of the qv file I made the attempt with a bar chart that has no dimensions, it is not what I need. I think the calculation can be made simpler, maybe from the same script I can create those combinations in a new dimension but I don't know how.

From already thank you very much.

Labels (4)
1 Solution

Accepted Solutions
fosuzuki
Partner - Specialist III
Partner - Specialist III

Hi,

check the attached app, see if this is what you are looking for. I created a dimension with the category combinations.

 

View solution in original post

9 Replies
fosuzuki
Partner - Specialist III
Partner - Specialist III

You can create one expresssion for each combination, and the expressions header can be pivoted in the table to show as columns or rows. So maybe you don't need a dimension...

Is there a reason why you need the combinations in a dimension? Do you want the users to use this dimension as a filter?

 

mahesh_agrawal
Creator
Creator

Hi,

 

Please find attached QVW and sample data file with solution.

Hope this works for you.

Cheers,

Mahesh

educastri83
Creator
Creator
Author

Thanks @mahesh_agrawal 

Something like this I imagine to have the category combos as dimension field. However, the calculation it performs on the pivot chart is not correct. For example, in your example UserID 1 has only the value "Category1" in the CategoryName field, so that user should not appear in the calculation of the combinations and it is because it only has a single category, it does not have more than one category to be able to arm the category combos. And so for other users.
Can the pivot chart expression count when a user has the combination of categories?

educastri83
Creator
Creator
Author

Hi @fosuzuki 

The data was from a dummy example, but in reality it can be many more categories. In the example I tried it with a listbox as a filter, which helps but I lose the option to see it in a summary. To be able to visualize it from a pivot chart that shows as a dimension all the combinations of categories and in the expression that counts the total number of users that have that combination is what I need.

I tested it with a bar chart and at least in the example there is an expression for each combination of categories. That bar chart has no dimensions and I lose interaction. I would also like to be able to order from highest to lowest the combination that has the highest number of users and I am not succeeding.

educastri83
Creator
Creator
Author

In fact, about the filter option, the calculation I did in a list box was the following:

 

Aggr(if(Count(DISTINCT {<Category={'Category 1'}, Granted ={'Y'}>} USERNAME)  >0
   and Count(DISTINCT {<Category={'Category 2'}, Granted ={'Y'}>} USERNAME)   > 0 , 'Category 1 & Category 2',
   
   
   if(Count(DISTINCT {<Category={'Category 2'}, Granted ={'Y'}>} USERNAME)  >0
   and Count(DISTINCT {<Category={'Category 3'}, Granted ={'Y'}>} USERNAME)   > 0 , 'Category 2 & Category 3',
   
   
   if(Count(DISTINCT {<Category={'Category 2'}, Granted ={'Y'}>} USERNAME)  >0
   and Count(DISTINCT {<Category={'Category 4'}, Granted ={'Y'}>} USERNAME)   > 0 , 'Category 2 & Category 4'
   
   ))), USERNAME)
  

 

 the above returns a list like:

List box
Category 1 & Category 2
Category 2 & Category 3
Category 3 & Category 4

 

It serves as a filter to see which user has which combination, but the truth is I lose the total summary that counts the total number of users who have each combo, which is basically what I need.

fosuzuki
Partner - Specialist III
Partner - Specialist III

Hi,

check the attached app, see if this is what you are looking for. I created a dimension with the category combinations.

 

educastri83
Creator
Creator
Author

Hi @fosuzuki  Thank you very much, you have helped me a lot and it's a great solution. I did the test with the real data and the pivot chart works as I wanted.

One last question, I would also like the listbox with the "CatCombo" field to be available on the dashboard, and when including it I have noticed that it brings some combos that users do not have, for example username 17 which has the values "Category 2" and "Category 4" and the listbox shows the combination "Category 1 and Category 2" and so on.. that username17 doesn't really have.
Is there a way for the listbox to show "Category 4 and Category 2" as a related value? something like that:

CatCombo
Category 4 and Category 2

note: Example when USERNAME 17 was selected

 

fosuzuki
Partner - Specialist III
Partner - Specialist III

Hi,

Actually the previous solution I sent you has two parts: some preprocessing in the script, and a part of the magic in the expression with aggr.

If you want a more direct filter behavior, I think that you'll need to change the data model, preprocessing the CatCombo and creating a field directly associated with the user.

educastri83
Creator
Creator
Author

Hi @fosuzuki , Thank you very much, you have helped me a lot. I'm going to try and play with the data, but the solution you gave me works for me. Thanks, again