7 Replies Latest reply on May 2, 2018 6:10 PM by bala kumar

    Data variance data based on different measures

    bala kumar

      Hi All,

       

      I need to do the data variance calculation for different measure. The date variance calculation is between max date & maxdate -7days and I need the output as below.

      I am attaching the TWBX file.

       

      It would be highly appreciated if someone could help me this output.

       

      Thanks,

      Bala

        • 1. Re: Data variance data based on different measures
          Paul Field

          Hi Bala,

           

          The easiest way to do this is to treat each measure separately (in a separate worksheet) - that way we can add the a calculated field based on the measure e.g for your rates calc I have created a calulated field "rate direction":

          IF ZN(SUM([Rate])) - LOOKUP(ZN(SUM([Rate])), -1) > 0 THEN "UP"

          ELSEIF ZN(SUM([Rate])) - LOOKUP(ZN(SUM([Rate])), -1) < 0 THEN "Down"

          ELSE "Steady"

          End

           

          We can then add to the shapes shelf (and to the color shelf) and then choose the appropriate shape (and color). Then the worksheets can be brought together in a dashboard.

           

          In the attached I have done this for rates, but the same can be applied to your other measures.

          • 2. Re: Data variance data based on different measures
            bala kumar

            Hi Paul,

             

            Thanks for the reply.

             

            In my case, How can i get the difference of the values as when I do the table calculation it doesn't provide me the same output format.

             

            Even If I calculate the difference in a separate sheet and i am not able to align the same in dashboard.

             

            Could you help on getting the difference & diff %

             

            Thanks,

            Bala

            • 3. Re: Data variance data based on different measures
              Paul Field

              Sure...here's the step by step process.

               

              Step 1: Simplify worksheet to a single measure:

               

              Step 2: Change measure to a difference (and/or % difference)

               

               

              Step 3: Drag rate back onto text shelf

               

               

              Step 4: Double click on the Table calc and copy the formula which is: ZN(SUM([Rate])) - LOOKUP(ZN(SUM([Rate])), -1)

               

              Step 5: Create a new calculated field “Rate direction” which uses this formula:

              IF ZN(SUM([Rate])) - LOOKUP(ZN(SUM([Rate])), -1) > 0 THEN "UP"

              ELSEIF ZN(SUM([Rate])) - LOOKUP(ZN(SUM([Rate])), -1) < 0 THEN "Down"

              ELSE "Steady"

              End

               

              Step 6: Drag “Rate Direction” onto the shape shelf. Ensure the table calc is set to compute as “table across” and select the appropriate shape for up/down/steady.

               

              Step 7: Drag “Rate direction” onto colour shelf and select colours for up/down/steady (choose white for steady so that it is not displayed against white background)

               

              Step 8 (optionally) remove the Table calc from the text shelf so the difference value isn’t displayed.

               

              Then if you create 3 sheets, one for each measure, you should be able to align in your dashboard no problem - we know they will all be 1 row and 2 columns, so shouldn't be an issue - unless I'm misunderstanding something?

              • 4. Re: Data variance data based on different measures
                bala kumar

                Thanks a lot Paul. Highly appreciated.

                 

                Will try the solution and update you on it.

                 

                Thanks,

                Bala

                • 5. Re: Data variance data based on different measures
                  bala kumar

                  Paul, In this case i need the difference value and the up/down symbol as a third column as like how we get in excel cell by cell difference.

                   

                  Regards,

                  Bala

                  • 6. Re: Data variance data based on different measures
                    Paul Field

                    We can do that again by splitting into different sheets. See attached. However, I would generally recommend on going the path of trying to replicate an excel file. It's often possible, but can add complexity and difficulty and there may just be a different way of looking at the data which is much simpler (and performs better) and gives the same answer, just in a slightly different way.

                    • 7. Re: Data variance data based on different measures
                      bala kumar

                      Thanks Paul. It all worked well.

                       

                      Apologies for the late reply.

                       

                      Regards,

                      Bala