2 Replies Latest reply on Jun 17, 2016 6:54 AM by Niranjan Banarjee

    Calculation vs. PY

    Niranjan Banarjee

      How to calculate vs. PY

        • 1. Re: Calculation vs. PY
          Simon Runc

          hi Niranjan,

           

          Is there a reason why you have a column for each month/year combination?

           

          Are you planning to create a parameter with every month/year combination

           

          Jan - 16

          Feb - 16

          March -16

           

          and then have a calculated field...

           

          Case [parameter.select month]

               WHEN 'Jan - 16' THEN [Jan - 16]

               WHEN 'Feb - 16' THEN [Feb - 16]

               WHEN 'March - 16' THEN [March - 16]

          ...

          END

           

          If you are (setting up each month's calc manually for each month) then you can use the above structure. You can then also create vs PY and vs PP variants of them

           

          eg.

          [vs PY]

          Case [parameter.select month]

               WHEN 'Jan - 16' THEN (sum([Jan-2016])-sum([Dec-2015]))

               WHEN 'Feb - 16' THEN (sum([Feb-2016])-sum([Jan-2016]))

               WHEN 'March - 16' THEN (sum([Mar-2016])-sum([Feb-2015]))

          ...

          END

           

           

          This is going to get very large very quickly, and you'll also need to manually update your parameter list every month (when the next months data comes in), as well as add that to the above CASE calculation. I would seriously look at changing the shape of your data so month goes down the page, and measures across. You are fighting how Tableau ideally wants data structured. If it was like this you wouldn't need to create a calculation for each month, and would be maintenance-free

          • 2. Re: Calculation vs. PY
            Niranjan Banarjee

            OK let me think over data structure