Percentage by Column by Dimension

[danboh_qsen]

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?

Thanks in advance!

[zzyjordan]

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]

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]

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]

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 · Accepted solution]

How about this to calculate percentage?

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

[danboh_qsen]

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.