Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
danboh_qsen
Contributor II
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:

Capture.PNG

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

Capture2.PNG

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))

Capture3.PNG

Any thoughts?

Thanks in advance!

 

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

How about this to calculate percentage?

Count(DISTINCT id)/Count(DISTINCT TOTAL <Name, Start_Month, Values> id)

View solution in original post

10 Replies
zzyjordan
Creator II
Creator II

Have you tried using Aggr() at the level of month?
below is the link from online help explaining the usage of aggr()
https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/aggr....

Hope this helps
ZZ
sunny_talwar

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

danboh_qsen
Contributor II
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. 

danboh_qsen
Contributor II
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

sunny_talwar

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).

 

danboh_qsen
Contributor II
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

sunny_talwar

How about this to calculate percentage?

Count(DISTINCT id)/Count(DISTINCT TOTAL <Name, Start_Month, Values> id)
danboh_qsen
Contributor II
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?

sunny_talwar

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.