7 Replies Latest reply on Jun 12, 2016 10:38 PM by Ashish Chaudhari

    % on YTD calculation

    kek soon yong

      Good day!

       

      I have a calculation problem that i'm unable to solve.

       

      So basically my source data is like this, and i wanted to calculate YTD sales based on date parameter selected.

       

       

      Example if i select 2/1/2016, the CY sales should be sum of CY sales from 1/1/2016 to 2/1/2016, which is working perfectly.

       

      However if i want to calculate YTD performance (Cy sales/ PY Sales) then i run into problem.

      The formula is calculating at record level and summing up, which is wrong.

       

      I've attach my workbook, hope to get some expert advice! Thanks

        • 1. Re: % on YTD calculation
          Ashish Chaudhari

          Hi Kek,

           

          Please find the attached result and confirm the output. I have observed that you are using old version of tableau. I am attaching the workbook which is of tableau 9.3 version.

           

          I have used below calculations.

           

          1. Filter which will restrict the values from 01/01/2016 to selected date in parameter. Place this in filter section and select true.

          [Date]<=[Parameters].[Date] and [Date]>=MAKEDATE(YEAR([Parameters].[Date]),01,01)

           

          2. YTD performance

          (SUM([CY Sales]))/(SUM([PY Sales]))

           

          3. YTD running performance %

          RUNNING_SUM(SUM([CY Sales]))/RUNNING_SUM(SUM([PY Sales]))

           

          4. Running CY sales or PY sales use the table calculations as below.

           

           

          Thanks and Regards,

          Ashish Chaudhari

          • 2. Re: % on YTD calculation
            Ashish Chaudhari

            Please find the attached workbook below but Im sure this won't open as you are using older version of tableau. Please refer to the calculations that I have pasted in previous post.

             

            -Thanks

            Ashish Chaudhari

            • 3. Re: % on YTD calculation
              kek soon yong

              Hi Ashish,

               

              Thanks for the quick reponse!

              however this is not the solution i need.

               

              I can't seem to attach files now, so let me try to explain using excel.

               

              Cell H2 is the parameter, so in this case if i select date 5/1/2016, my current YTD PY Sales (C8) will be the sum of C2 to C6

              and current YTD CY sales (D8) will be sum of D2 to D6.

              Tableau is doing it fine, see 2nd screenshot as the numbers tally.

               

              However the problem appears when i want to calculate YTD performance.

              Tableau seems to be summing up column E2 to E6, thus getting 31.7%.

              What i need is 6.6% instead (sum of D2:D6)/sum of (C2:C6). Thanks

               

              • 4. Re: % on YTD calculation
                Ivan Young

                Hi Kek,

                Could you list your expected values for a few or all of your parameter selections?   It's pretty easy to get the number you are looking for 5/1.

                 

                Regards,

                Ivan

                 

                • 5. Re: % on YTD calculation
                  Ashish Chaudhari

                  Hi Kek,

                   

                  The calculations that I have shown above in the first post is doing same thing. Refer to the screenshot below. (Red coloured box) Its giving you the same out put. How ever you can go ahead and subtract 1 from it to get the desired result you want.

                  Check the below screenshot for the same.

                   

                  I have changed YTD performance Running as below

                  (RUNNING_SUM(SUM([CY Sales]))/RUNNING_SUM(SUM([PY Sales])))-1

                   

                  You can go ahead and remove the months if you want. You will get the desired result.

                   

                  -Ashish

                  1 of 1 people found this helpful
                  • 6. Re: % on YTD calculation
                    kek soon yong

                    Hi Ashish,

                     

                    Yes that's what i needed. Thanks so much!

                    • 7. Re: % on YTD calculation
                      Ashish Chaudhari

                      Hi Kek,

                       

                      If your queries are resolved then can you please mark the appropriate response as correct and close the thread. This will help many who are looking for this solution.

                       

                      Thanks and Regards,

                      Ashish Chaudhari