Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kmstephenson
Creator
Creator

bar chart trends, percentages of bars incorrect

Hi All,

I am working on a stacked bar chart to chart trends of entitlement reason for a patient population beginning with rolling year ending in Dec 2013.
I have set up the bar chart as follows:

dimensions:

IF(EndDt>'2013-11-30', MonthName(EndDt), )

PrimEntlmtReason

expression:

COUNT({<EndDt>} DISTINCT  PAT_ID) /COUNT(TOTAL DISTINCT PAT_ID)

What needs to be changed in the expression so that it divides by the total number of patients for the bar EndDt time frame? Currently the percentages are off for every date but the date selected in the EndDt listbox because it is dividing be the number of patients in the EndDt selected by a list box. See example graph- All of these should total to 100% for ever time frame selected in the EndDt listbox.

Additionally, I would also like to be able to select a bar (any date date) on the x-axis and have that date selected filter the dashboard. Currently this is not working. Is this because I am using a calculated dimension for the date (to format the date differently and only graph dates beginning Dec 2013)?

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

Ok - it worked for me on a sample data set. How about changing slightly to this to ensure the expression accurately compares a date with a date ?

If(  Date(EndDt) > makedate(2013,11,30) , MonthName(EndDt) )   as Month

Also, if you don't want to do it in the script, can you tell me which list box (field) you are selecting for 'May 2014'  ? You mentioned doing that 2 posts above ....  so if you make a selection in the list box, which field is filtered in the 'current selections' ?  For the SET ANALSYIS approach you would use THAT field and not the 'month' field i put in the sample, then the expression would 'ignore' the selection made in the list box.

View solution in original post

9 Replies
JonnyPoole
Employee
Employee

Does this work for your 1st question ?

COUNT(  DISTINCT  PAT_ID)

/

COUNT(TOTAL <EndDt> DISTINCT PAT_ID)

kmstephenson
Creator
Creator
Author

It accurately calculates the %s but only shows the bar for the end date selected in the list box. For example, only shows May 2014 bar when May 2014 is selected in list box. Given it is my trends graph, I want all the months in the attached graph example to show (for every end date selected- end date has always only one selected value).

JonnyPoole
Employee
Employee

You could add a set modifier to prevent the expression from responding specifically to the month field filter.


If month is the field name it would look like this :


COUNT( {$<month=>} DISTINCT  PAT_ID)

/

COUNT( {$<month=>}  TOTAL <EndDt> DISTINCT PAT_ID)

kmstephenson
Creator
Creator
Author

I don't have a month field, I have the calculated dimension:

IF(EndDt>'2013-11-30', MonthName(EndDt), )

I tried creating this calculated dimension first in the script and calling the field TrendsEndDt and then using that in your code in place of month but it resulted in one bar for May 2014 and all the values were off in the pop ups.

EndDt is essentially the same thing as the calculated dimension, only I wanted the chart to only show end dates beginning Dec 2013 and I also wanted the end date formatted differently, which is why I used the calculated dimension instead of just EndDt.

JonnyPoole
Employee
Employee

Ok - it worked for me on a sample data set. How about changing slightly to this to ensure the expression accurately compares a date with a date ?

If(  Date(EndDt) > makedate(2013,11,30) , MonthName(EndDt) )   as Month

Also, if you don't want to do it in the script, can you tell me which list box (field) you are selecting for 'May 2014'  ? You mentioned doing that 2 posts above ....  so if you make a selection in the list box, which field is filtered in the 'current selections' ?  For the SET ANALSYIS approach you would use THAT field and not the 'month' field i put in the sample, then the expression would 'ignore' the selection made in the list box.

kmstephenson
Creator
Creator
Author

Thank you! This worked for fixing the percentages. I substituted EndDt for Month in the equation and it worked. Why is it that I need EndDt twice in the denominator? In the SET ANALYSIS and in between the TOTAL and DISTINCT?

Why is it though that if I click on any of the bars in the chart "Feb 2014" bar that it doesn't update the EndDt in the list box or my selections to that date? Is it because I am using a calculated dimension even though it is stemming from the same var, EndDt?

JonnyPoole
Employee
Employee

For the first question i take it your denominator ended up looking like this ?

COUNT(  {$<EndDt=>}   TOTAL <EndDt> DISTINCT PAT_ID)


The EndDt in the SET EXPRESSION is a modifier. It is only there to modify the user's selections .  The ' = nothing  ' means to bsacially ignore any user selection on EndDt. The result is that the chart is NEVER filtered by EndDt and all EndDts show up showing a full trend at all times.   The 2nd part  TOTAL <EndDt>  is syntax to subtotal the expression for each EndDt.  It will total up all the stacks in the bar to give a subtotal for the whole bar which represents an EndDt....so two different things.


The 2nd part i'm not sure... i would expect that even though the chart doesn't flinch and shows all EndDts,  a selection would still be made in the current selections. Is the chart set to 'read only' on the general tab of the chart properties ?

kmstephenson
Creator
Creator
Author

No the read only is not selected. I also tried substituting the calculated dimension for EndDt to see if it worked if the first dimension was EndDt (direct link to list box) but this did not work either.

JonnyPoole
Employee
Employee

possible to share a QVW ?