4 Replies Latest reply on Aug 25, 2016 7:37 PM by chun wey teo

    How to convert YTD in each category into percentage?

    chun wey teo

      Hi,

      Tried online but to no valid.

       

      Can anyone help to guide on the following:

       

      1.  Use Tableau built-in YTD total and sort them by Category by month.

      2.  How to change the absolute number into percentage based on the previous month to see the increment percentage?

       

      Anyone can help on this?

       

      Thank you.

       

      Rdgs

      Chun Wey

        • 1. Re: How to convert YTD in each category into percentage?
          Stoyko Kostov

          Hi, Chun Wey,

           

          You shouldn't try to replace the YTD total with month-over-month (MOM) percent difference. The 2 indicators have different purposes.

           

          For the % diff, you should use Tableau's built-in table calculation. Let's say you want the MOM % diff on Sales. You can duplicate the Sales measure, drag it to Text, and right-click on the copy and choose Quick Table Calculation - % difference (assuming your data is already partitioned by month).

           

          You can still keep the YTD total and sort by it.

           

          Let me know if this doesn't make sense. Please attach a sample workbook in that case.

          • 2. Re: How to convert YTD in each category into percentage?
            chun wey teo

            Hi Stoyko,

             

            thanks for the response.

             

            You are right, I should not mixed them up.  Customer was asking for YTD % and was thinking the denominator based from previous time period.

             

            YTD % was refer to the based denominator since the start of period and not based from previous time period.

            So in this context, it would make more sense to get the Jan data and use as denominator for each coming months.  Right now, just need to find the formula for this and it should get the job done.

             

            Thanks!

            • 3. Re: How to convert YTD in each category into percentage?
              Stoyko Kostov

              Then what may work is the Tableau built-in table calculation called Percent of Total?

               

              If you have data from previous years and your customer wants the YTD% just for the current year, you can filter out the previous years. If you still need to show them, but the calculation is performed only on the current year, you can split into 2 sheets with different filters and then combine them in a dashboard.

               

              Again, if you give me some sample data, I think I'll be able to help you better.

              • 4. Re: How to convert YTD in each category into percentage?
                chun wey teo

                Hi Stoyko,

                 

                I think I got it.  Went to search for YTD% definition, it is based on the first month or first FY month as the base denominator for the coming months.   Example, Jan month shows 100 sales, Feb shows 200 sales, Mar shows 50 sales made.  YTD % should reflect Jan 0%, Feb 100% and Mar -50%. 

                 

                This can be done, via percentage difference.  Choose pane across to run calculation each category and based calculation on First.

                 

                This will ensure calculation is run for each pane based on the first month.

                 

                As for the sample data, apology for it as I was working on actual data.

                 

                Thank you so much for your help!