topic Re: Calculated Dimension on a Pivot Chart in QlikView

Calculated Dimension on a Pivot Chart

educastri83 — Sat, 16 Nov 2024 00:11:04 GMT

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.

Re: Calculated Dimension on a Pivot Chart

fosuzuki — Fri, 07 Aug 2020 08:13:04 GMT

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?

Re: Calculated Dimension on a Pivot Chart

mahesh_agrawal — Fri, 07 Aug 2020 09:27:18 GMT

Hi,

Please find attached QVW and sample data file with solution.

Hope this works for you.

Cheers,

Mahesh

Re: Calculated Dimension on a Pivot Chart

educastri83 — Fri, 07 Aug 2020 14:08:40 GMT

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?

Re: Calculated Dimension on a Pivot Chart

educastri83 — Fri, 07 Aug 2020 14:26:46 GMT

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.

Re: Calculated Dimension on a Pivot Chart

educastri83 — Sat, 08 Aug 2020 01:58:31 GMT

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.

Re: Calculated Dimension on a Pivot Chart

fosuzuki — Mon, 10 Aug 2020 12:57:36 GMT

Hi,

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

Re: Calculated Dimension on a Pivot Chart

educastri83 — Tue, 11 Aug 2020 03:30:05 GMT

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

Re: Calculated Dimension on a Pivot Chart

fosuzuki — Tue, 11 Aug 2020 21:48:39 GMT

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.

Re: Calculated Dimension on a Pivot Chart

educastri83 — Tue, 11 Aug 2020 22:27:36 GMT

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