4 Replies Latest reply on Jul 16, 2017 7:00 PM by w w

    How to build complicated calculation

    w w

      HI,

       

      I'am using Tableau 9.3. I

       

       

       

      As you can see in Picture I. Each pane 8 bars represent 8 city's money inflow MTD (month to date) and I have 4 period end data, 20170616,20170623,20170630,20170707 (all Friday PE).

       

      What I am goting to achieve is to build percentage change within the month. I know how to build percentage change for a serial time period as show in picture 2.

       

      But the problem is MTD, the data are MTD data, so the data should be compared within month. for eg, 20170616,20170623,20170630 will be compared, 20120707 should be compared with 20170714 data.

       

      My question is how to write those complicated calculation. Thanks.

       

      Regards,

      Dawn

       

       

        • 1. Re: How to build complicated calculation
          Mavis Liu

          Hi Dawn,

           

          You can actually use table calculations to work out the percentage differences.

           

          First I have broken down the date values so that it gets separated into parts (you can create date parts, but in my example I have just split it out):

           

          So to take the year part, I have created the calculation:

          TRIM(LEFT(str([Period]),4))

           

          Then I did the same with the month. The reason why I did this was to create particular panes to capture all the 20160616, 20170623 etc values under the same month.

           

          Bringing it into the view I see this:

           

           

           

          You can add in the date part month into colour in the marks card if you've like it to be distinguished.

           

          So what you can do next is to drag your measure PE Dept.Inc (if this is the value you'd like to analyse the changes) into text on the marks card. Then right click on the measure and add a table calculation:

           

           

          Then add in the table calculation 'Percent Difference From' and then select Specific Dimensions and across your 'version' or time period dimension:

           

           

           

          This will compare the periods against one another (within the same month). You should then fine the text labels are showing the percentage differences.

           

          In Tableau 9.3, I can't remember whether you have the highlighted calculation assistance. But the version I'm using at the moment is 10.2 and you can see it shows you which points they are calculating.

           

          Thanks,

           

          Mavis

          InterWorks Europe

          • 2. Re: How to build complicated calculation
            Mavis Liu

            Hi Dawn,

             

            Apologies the last post didn't include all my images. Let's try again! :

             

            You can actually use table calculations to work out the percentage differences.

             

            First I have broken down the date values so that it gets separated into parts (you can create date parts, but in my example I have just split it out):

             

            So to take the year part, I have created the calculation:

            TRIM(LEFT(str([Period]),4))

             

            Then I did the same with the month. The reason why I did this was to create particular panes to capture all the 20160616, 20170623 etc values under the same month.

             

            Bringing it into the view I see this:

            2017-07-14_09h43_09.png

             

             

            You can add in the date part month into colour in the marks card if you've like it to be distinguished.

             

            So what you can do next is to drag your measure PE Dept.Inc (if this is the value you'd like to analyse the changes) into text on the marks card. Then right click on the measure and add a table calculation:

             

             

            Then add in the table calculation 'Percent Difference From' and then select Specific Dimensions and across your 'version' or time period dimension:

             

            2017-07-14_09h34_48.png

             

            This will compare the periods against one another (within the same month). You should then fine the text labels are showing the percentage differences.

             

            In Tableau 9.3, I can't remember whether you have the highlighted calculation assistance. But the version I'm using at the moment is 10.2 and you can see it shows you which points they are calculating.

             

            Thanks,

             

            Mavis

            InterWorks Europe

            • 3. Re: How to build complicated calculation
              Mavis Liu

              Hi Dawn,

               

              Hopefully you were able to get the table calculation working.

               

              Once you did that, hopefully you were then able to get the marks and up/down arrows working as you wanted. It should look similar to the below:

               

               

              2017-07-14_16h34_54.png

               

              For the above I used a dual axes. The up and down arrows were based on a calculation, if the percent change was >0 then 'Up' etc. As the first month of every period doesn't have a comparison against it, I have also set these to blank. Again this was done with a calculation.

               

              If you need any help with that, then please let me know.

               

              Thanks,

               

              Mavis

              • 4. Re: How to build complicated calculation
                w w

                HI Mavis,

                 

                Sorry for the late reply. Thank you so much. It works.

                 

                Regards,

                Dawn