Announcements
cancel
Showing results for
Did you mean:
Contributor II

Percentage by Column by Dimension

Hello,

I just started working with Qlik Sense and have been experimenting with some dashboards and calculations. The most recent one is to show the percentage for a column for a dimension.

Here's what I have:

And this is roughly what I'd like to get:

I can get to the point of getting the total for each dimension, but it's totaled for all months, and not for each individual month, using

`COUNT(TOTAL <dimension> distinct(id))`

Any thoughts?

Labels (1)
• column

1 Solution

Accepted Solutions
MVP

`Count(DISTINCT id)/Count(DISTINCT TOTAL <Name, Start_Month, Values> id)`
10 Replies
Creator II
Have you tried using Aggr() at the level of month?
https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/aggr....

Hope this helps
ZZ
MVP

What all expressions and dimensions are you using in your chart? Can you list them out here?

Contributor II
Author

I've tried several combinations but haven't found the right one. For example, this makes sense to me:

`count(aggr(TOTAL <dimension> distinct(id),date))`

but it returns 0. I'd like to get at least the total for the two columns (No and Yes) so I can get the percent.

Contributor II
Author

Dimension:

Name

Columns:

Month([Start_date]), Values, No/Yes

Measures:

TOTAL = Count(distinct id)
% = COUNT(TOTAL <dimension> distinct(id)) //This is all I have so far

MVP

Questions

@danboh_qsen wrote:

Month([Start_date]), Values, No/Yes

Values and No/Yes are dimensions? I am confused?

@danboh_qsen wrote:

% = COUNT(TOTAL <dimension> distinct(id)) //This is all I have so far

<dimension>? Which dimension? so generic? can you try to give the exact definitions you are using for your expressions

Finally, can you create a new field for Month in the script instead of doing this on the front end?

`Month([Start_date]) as Start_Month`

and now use this as one of your columns instead of Month(Start_Date).

Contributor II
Author

Sorry, I'll try to be more specific. I've created the new field Start_Month as suggested.

Dimensions:

Rows: Name

Columns: Start_Month, Values, Checked_In

Measures:

TOTAL = Count(distinct id)
% = COUNT(TOTAL <Name> distinct(id))  //This is all I have so far

MVP

`Count(DISTINCT id)/Count(DISTINCT TOTAL <Name, Start_Month, Values> id)`
Contributor II
Author

This absolutely worked! Thank you very much!

So I guess the only thing that I was missing was adding the month to the total calculation. But I remember trying this yesterday with the autocalendar (like Start_date.autoCalendar.Month) but the results weren't accurate.

Any final thoughts on this?

MVP

Derived fields have shown to give a lot of issues like this and in set analysis... I would recommend not to use them in cases like these and instead create your own calendar.