9 Replies Latest reply: Aug 18, 2016 2:19 PM by Lauren Mills RSS

    Drill-down Expression

    Lauren Mills

      Question -

       

      I have a drill-down bar chart that shows (Retention %).

       

      My Logic -

       

      I have 2 variables:

       

      $(vRQ1B)

      $(vRQ1E)

       

      I divide by both variables to get my (Retention %).

       

      $(=vRQ1E)/$(vRQ1B)

       

      See Example Below -

       

      I have Brazil selected and on the lower right had side the % of 93 is correct. Why are all the other locations showing an average when I created a drill-down?

       

       

       

      vRQ1E:

      =Count({<[Dealer Hire Date] = {'>=01/01/2016<=03/31/2016'}>}[Student ID]) - count({<[Dealer Hire Date] = {"$(= '>=' & '01/01/2016' & '<=' & '03/31/2016')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2016' & '<=' & '03/31/2016')"}>} [Student ID])

       

      vRQ1B:

      =Count({<[Dealer Hire Date] = {'>=01/01/2016<=03/31/2016'}>}[Student ID])

        • Re: Drill-down Expression
          Sunny Talwar

          Have you already clicked on the green tick mark? May be it will go away once you click on it

            • Re: Drill-down Expression
              Lauren Mills

              What's the green tick mark? The Check Mark?

               

               

              It works fine when I make the selection but I want to show a ranking amongst them all to show the top location and their retention.

                • Re: Drill-down Expression
                  Sunny Talwar

                  Right the check mark. I am not sure I understand what you trying to do. Can you may be trying explaining again?

                    • Re: Drill-down Expression
                      Lauren Mills

                      I want it to look like the above ^. However, that calculation is not showing what I want. If you look back up at the first part of this thread you'll see my calculation is showing an average across the board. Each location should have their own Retention % and not show an average of all locations. They should all have their own percent's which would then average out to 89% in this example.

                       

                      I think it has something to do with dividing by variables for the drill-down. But I'm not sure.

                       

                      I want the End Population / Beginning Population = Retention % (which I explained my logic above).

                       

                      Does think make more sense?

                        • Re: Drill-down Expression
                          Sunny Talwar

                          Got it, would you be able to try out one of these variations:

                           

                          vRQ1E/vRQ1B


                          or


                          $(vRQ1E)/$(vRQ1B)


                          or


                          $(=vRQ1E)/$(=vRQ1B)

                            • Re: Drill-down Expression
                              Lauren Mills

                               

                              They all are still returning the average. Below is the logic I want to represent.

                               

                              Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID]) - count({<[Dealer Hire Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"}>} [Student ID])  /   Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID])

                               

                               

                              Maybe you'll see something that's restricting the restriction on showing per county.

                                • Re: Drill-down Expression
                                  Lauren Mills

                                   

                                  Using this expression ^ this is what I get Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID]) - count({<[Dealer Hire Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"}>} [Student ID])  /   Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID])