Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
How about this to calculate percentage?
Count(DISTINCT id)/Count(DISTINCT TOTAL <Name, Start_Month, Values> id)
What all expressions and dimensions are you using in your chart? Can you list them out here?
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.
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
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).
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
How about this to calculate percentage?
Count(DISTINCT id)/Count(DISTINCT TOTAL <Name, Start_Month, Values> id)
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?
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.