8 Replies Latest reply on Mar 1, 2017 1:42 AM by Permata Russiana

    YoY MoM YTD (%) side-by-side in Table

    Permata Russiana

      Dear All,

       

      I am very new in Tableau, sorry if someone have asked this before but so far I haven't found the same topic with my question

       

      Can Tableau show YoY% MoM% YTD% side-by-side in one table and use Month/Year as filter to identify current month?

      I have tried to use YoY and YTD calculation in Tableau but so far I can't combine it in one table

       

      Here I attach my workbook 10.1 (monthly period) and screenshot of expected result

       

      Appreciate your help!

       

      Thank you

        • 1. Re: YoY MoM YTD (%) side-by-side in Table
          Shinichiro Murakami

          Hi Permata

           

          Assuming today is "Paramter", in this case 2016/7/1.

          And assuming the "this month" = last full month before today.

           

           

           

          Several calculated field need to be created.

           

          [Last Month]

          if datetrunc('month',[Periodcod21]) = datetrunc('month',dateadd('month',-2,[Today]))

          then [Salesvolume] end

           

          [Last Year Same Month]

          if datetrunc('month',[Periodcod21]) = datetrunc('month',dateadd('month',-13,[Today]))

          then [Salesvolume] end

           

          [This Month]

          if datetrunc('month',[Periodcod21]) = datetrunc('month',dateadd('month',-1,[Today]))

          then [Salesvolume] end

           

          [YTD last year]

          if datetrunc('year',[Periodcod21]) >= datetrunc('year',dateadd('month',-12,[Today]))

          and datetrunc('month',[Periodcod21]) < datetrunc('month',dateadd('month',-12,[Today]))

          then [Salesvolume] end

           

          [YTD this year]

          if datetrunc('year',[Periodcod21]) >= datetrunc('year',[Today])

          and datetrunc('month',[Periodcod21]) < datetrunc('month',[Today])

          then [Salesvolume] end

           

           

           

          [M o M]

          {fixed [Area1],[Brand]:sum([This Month])}/{fixed [Area1],[Brand]:sum([Last Month])}-1

           

          [Y O Y]

          {fixed[Area1],[Brand]:sum([This Month])}/{fixed[Area1],[Brand]:sum([Last Year Same Month])}-1

           

          [YTD]

          {fixed[Area1],[Brand]:sum([YTD this year])}/{fixed[Area1],[Brand]:sum([YTD last year])}-1

           

           

           

          Thanks,

          Shin

          4 of 4 people found this helpful
          • 2. Re: YoY MoM YTD (%) side-by-side in Table
            Permata Russiana

            Hi Shin,

             

            Thank you for your help!

             

            One question to confirm, can we have show monthly as filter instead of daily (as calender)?

             

            Kind Regards,

            Permata

            • 3. Re: YoY MoM YTD (%) side-by-side in Table
              Shinichiro Murakami

              If you are talking about parameter, No, it's date basis.

              If you need yyyy-MMM type parameter, you need to create that particular field.

               

              Like below.

               

              [yyyy-MMM]

              str(year([date field])+"-"+datename('month',[date field])

               

              In that case, couple of more calculation need to be added all the date related calc.

              I mean you need to convert above string to date something like this

               

              [convert back to date]

              datetrunc('month',dateparse("yyyy-MMM",[yyyy-MMM]))

               

              Thanks,

              Shin

              1 of 1 people found this helpful
              • 4. Re: YoY MoM YTD (%) side-by-side in Table
                Permata Russiana

                I see,  let me proceed with date.

                 

                Is that possible to have value Gain/Drop for each MoM, YoY, YTD?

                I have tried to use available calculation from Tableau (Percent Difference) but seems the figure isn't right

                 

                Thank you

                • 5. Re: YoY MoM YTD (%) side-by-side in Table
                  Shinichiro Murakami

                  Simply below,??

                   

                  [M o M delta]

                  {fixed [Area1],[Brand]:sum([This Month])}-{fixed [Area1],[Brand]:sum([Last Month])}

                   

                  [Y O Y delta]

                  {fixed[Area1],[Brand]:sum([This Month])}-{fixed[Area1],[Brand]:sum([Last Year Same Month])}

                   

                  [YTD elta]

                  {fixed[Area1],[Brand]:sum([YTD this year])}-{fixed[Area1],[Brand]:sum([YTD last year])}

                   

                   

                  If not, could you illustrate your additional needs more specifically?

                   

                  Thanks,

                  Shin

                  • 6. Re: YoY MoM YTD (%) side-by-side in Table
                    Permata Russiana

                    Hi Shin,

                     

                    Sorry for delay response, that was very helpful, actually the Gain/Drop I mentioned above is in percentage.

                     

                    if I am not mistaken, the solution you gave was for growth right? how can we create YoY% MoM% YTD% for market share?

                     

                    Thanks a lot,

                    • 7. Re: YoY MoM YTD (%) side-by-side in Table
                      Shinichiro Murakami

                      HI Permata

                       

                      I'm sorry I don't understand your question, what do you mean by market share?

                      I cannot imagine that measure thru given data.

                       

                       

                      Thanks,

                      Shin

                      • 8. Re: YoY MoM YTD (%) side-by-side in Table
                        Permata Russiana

                        Hi Shin,

                         

                        Sorry for confusing, I mean market share which calculate the value per brand with total value(all brand), so we got 100% in total.

                         

                        Example :

                        Market Share YTD Brand A (current month) = Brand A in Current Month / Total Brand A in YTD

                         

                        Thank you

                        Best