2 Replies Latest reply: Jan 12, 2018 11:14 AM by Maren Amthor 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 Babu

          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