9 Replies Latest reply: Oct 31, 2014 2:05 PM by Jonathan Poole RSS

    bar chart trends, percentages of bars incorrect

    Kristina Stephenson

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

        • Re: bar chart trends, percentages of bars incorrect
          Jonathan Poole

          Does this work for your 1st question ?

           

          COUNT(  DISTINCT  PAT_ID)

          /

          COUNT(TOTAL <EndDt> DISTINCT PAT_ID)

            • Re: bar chart trends, percentages of bars incorrect
              Kristina Stephenson

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

                • Re: bar chart trends, percentages of bars incorrect
                  Jonathan Poole

                  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)

                    • Re: bar chart trends, percentages of bars incorrect
                      Kristina Stephenson

                      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.

                        • Re: bar chart trends, percentages of bars incorrect
                          Jonathan Poole

                          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.

                            • Re: Re: bar chart trends, percentages of bars incorrect
                              Kristina Stephenson

                              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?