2 Replies Latest reply: Mar 23, 2018 4:32 PM by Ceci Land RSS

    Get percentage of one group based on another selected group

    Ceci Land

      I was asked today if it were possible to show on a chart not just the percentage of a group versus the total for the entire population, but instead show the percentage of a group versus a different selected group used as a filter.

       

      IOW, suppose I have a field called DroppedStatus with 4 values, SelfDrop, PaymentDropped, Recovered, NeverDropped.  I use these as a filter to show Online Course engagement.  So in the bar chart for online classes, I will show results for ExclusivelyOnline, SomeOnline, and NoOnline Classes.

       

      What I'd like to see is when I click on SelfDrop, the percentage shown on each of the 3 online class statuses will reflect not the percentage versus all students, but the percentage of the just the SelfDrop cohort. Currently, each of the online statuses show the percentage who are dropped relative to the entire ID count. My expression is: Num((Count(GCID)*100/Count({1}total GCID))/100,'##0.0%')

       

      I apologize if this is really simple.  I'm new to Qlik and I admit the expression syntax is confusing to me so far.  It appears that I need to specify the field and value to use for the counts, but if I do that, the visualization will be static.  Do I need to find a way to have the selection of the Dropped Cohort be a variable??  If so how in the word do I pass that to Online Status chart and not affect other sheets in my application?

       

      Thanks for any help.

      Ceci

        • Re: Get percentage of one group based on another selected group
          Sunny Talwar

          May be this

           

          Num((Count(GCID)*100/Count({1<DroppedStatus = {'SelfDrop'}>} TOTAL GCID))/100,'##0.0%')

            • Re: Get percentage of one group based on another selected group
              Ceci Land

              Thx!  This gave me a great start.  Simple syntax is tripping me up at the moment.

               

              A sample expression I came up with based on your suggestion looks like

               

              Num((Count({1<OnlineStatus = {'Exclusively Online Classes'}>} TOTAL GCID)/Count({1<DROP = {'NeverDropped'}>} GCID)))

               

              At the moment, I'm not worried about whether percentage is showing so I removed the formatting.  Basically, I want to calculate the percentage of certain entries in one group relative to the number of entries in a second group.  Exclusively online students are a subset of those who never dropped their classes.  So in this case, the entirety of "neverdropped" is my "total".

               

              The question I've been asked is what percentage of "never dropped" students are taking exclusively online classes?  Teh next question is, Which percentage of students who were "recovered" (formerly dropped) signed up for exclusively online classes?

               

              If I use an expression like the above though, it looks like it could only create a static number.  Is there a way to create a variable or some process to make the percentage show based on the filter selected instead of percentage versus the overall total of records?

               

              I hope that made sense.

               

              Thx again!