3 Replies Latest reply: Jan 17, 2018 12:20 PM by Kristina Stephenson RSS

    Using TOTAL in trending?

    Kristina Stephenson

      Hi All,

       

      I use the following expression to calculate % for any given EndDt selected (always one selected value):

       

      Expression:

      COUNT(DISTINCT Key)/COUNT(TOTAL DISTINCT Key)


      If I use this for trending in a straight table (i.e. mini chart sparkline) it seems to give me the correct sparkline pattern but not the correct values - I believe this is because how total works. Can someone help explain how this is working?

       

       

       

       

       

       

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

        • Re: Using TOTAL in trending?
          Anil Samineni

          The above expression works as Count the Unique number of Key and there TOTAL validity. So, Example

           

          Key

          1

          2

          3

          4

          1

          2

           

          Count(DISTINCT Key) -- 4 Where Count(TOTAL DISTINCT Key) also 4 until unless DISTINCT not presence. 4/4 = 1

          Count(TOTAL Key) always counts all even unique included like 6

           

          Same as below one, But IT ignores the irrespective of selection for EndDt ??

           

          Does this Helps?

          • Re: Using TOTAL in trending?
            Maren Amthor

            Hi,

             

            The Total is ignoring your dimension and in this example doing a sum and a count over all values in a table.

             

            Dim Value      sum(Value)      sum(Total value)      count(Distinct Value)      count(Total Value)

            1     100          100                    600                         1                                   3

            2     200          200                    600                         1                                   3

            3     300          300                    600                         1                                   3

             

            A TOTAL{<Dim>} is aggregating the values for the Dimension 'Dim':

             

            Dim  Value      sum(Value)      sum(Total{<Dim>} value)          

            1      100          100                    100                                  

            2      500          500                    700                                 

            2      200          200                    700                                 

            3      300          300                    300                                 

            • Re: Using TOTAL in trending?
              Kristina Stephenson

              I misspoke above - the sparkline patterns are off but the calculations for each EndDt are correct.  Here is some example data - the key is a concatenation of an ID and end date.

               

              Key                 EndDt                 Numerator

              A_201312       2013-12-31        1

              A_201403       2014-03-31        1

              A_201406       2014-06-30        1

              A_201409       2014-09-30        1

              A_201412       2014-12-31        1

              A_201503       2015-03-31        1

              A_201506       2015-06-30        1

              A_201509       2015-09-30        1

              A_201512       2015-12-31        1

              A_201603       2016-03-31        1

              A_201606       2016-06-30        1

              A_201609       2016-09-30        1

              A_201612       2016-12-31        1

              A_201703       2017-03-31        1

              A_201706       2014-06-30        1

              B_201509       2015-09-30        0

              B_201512       2015-12-31        1

              B_201603       2016-03-31        1

              B_201606       2016-06-30        1

              B_201609       2016-09-30        1

              B_201612       2016-12-31        1

              B_201703       2017-03-31        1

              B_201706       2017-06-30        1

               

              The sparkline that I'm seeing is as follows:

               

              This is the trendline shape that I should be seeing:

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

               

              Anyone have any idea why this is going on? I validated that the expression is working by computing the above expression with hardcoded EndDts in the set analysis - I get the expected values but the plot doesn't show this in the sparkline of the straight table.

               

              Thanks!

              Tina