4 Replies Latest reply on Feb 26, 2017 10:54 PM by Zorikto Dabaev

    How to create calculation of YoY change in % for multiple measures in 1 table

    Zorikto Dabaev

      Hello, community!

      I need help with calculation of YoY change in % for multiple measures in 1 table

      Please check screen shot - I showed what I need exactly

      Checked different sources and I was able to do only for 1 measure, not multiple.

      Maybe somebody already done the same? Looking for any tip.

       

      Capture.PNG

        • 1. Re: How to create calculation of YoY change in % for multiple measures in 1 table
          Shinichiro Murakami

          Hi Zorikto

           

          The standard(?) or easier way is to create two worksheets and merge on Dashboard.

          According to your comment you want to create the table on single worksheet, so here is that option.

          However, this approach is quite a bit troublesome...

           

          Assuming you need 4 measures, you need to create 4x3 = 12 fields.

          And to make the view better, you need to duplicate the data source and create total three.

          From edit data source, create union and add three tables.

           

          Create 4x3=12 calculated fields.

           

          [Order Qty 1]

          if attr([Table Name])="Orders" then sum([Order Quantity]) end

           

          [Profit 1]

          if attr([Table Name])="Orders" then sum([Profit]) end

           

          [Sales 1]

          if attr([Table Name])="Orders" then sum([Sales]) end

           

          [Shipping Cost1]

          if attr([Table Name])="Orders" then sum([Shipping Cost]) end

           

          [Order Qty YOY]

          if attr([Table Name])="Orders1" then

          (zn(sum([Order Quantity]))-zn(lookup(sum([Order Quantity]),-1)))/

          abs(zn(lookup(sum([Order Quantity]),-1))) end

           

          [Profit YOY]

          if attr([Table Name])="Orders1" then

          (zn(sum([Profit]))-zn(lookup(sum([Profit]),-1)))/

          abs(zn(lookup(sum([Profit]),-1))) end

           

          [Sales YOY]

          if attr([Table Name])="Orders1" then

          (zn(sum([Sales]))-zn(lookup(sum([Sales]),-1)))/

          abs(zn(lookup(sum([Sales]),-1))) end

           

          [Shipping Cost YOY]

          if attr([Table Name])="Orders1" then

          (zn(sum([Shipping Cost]))-zn(lookup(sum([Shipping Cost]),-1)))/

          abs(zn(lookup(sum([Shipping Cost]),-1))) end

           

          [Order QTY Title]

          if [Table Name]="Orders2" then "Order Quantity" end

           

          [Profit Title]

          if [Table Name]="Orders2" then "Profit" end

           

          [Sales Title]

          if [Table Name]="Orders2" then "Sales" end

           

          {Shipping Cost Title]

          if [Table Name]="Orders2" then "Shipping Cost" end

           

           

          Create header for filter (hide last year only from title and YOY)

           

          [Header filter]

          [Table Name]+"_"+str(Year([Order Date]))

           

          Create Header for title.

           

          [Header Title]

          if ([Table Name])="Orders2" then "Measure"

          elseif

          ([Table Name])="Orders" then str(year([Order Date]))

          elseif

          ([Table Name])="Orders1" then "YOY"

          end

           

           

          Put 4x Min of number of record (this is place holder with number "1")

           

           

          Put each quantity related three fields to top mark.

           

           

           

           

           

          Hide Header of last year from title and YOY

           

           

          Hide Header of "Table name" and "Header Filter"

           

           

           

          Thanks,

          Shin

          1 of 1 people found this helpful
          • 2. Re: How to create calculation of YoY change in % for multiple measures in 1 table
            Zorikto Dabaev

            Thank you, Shin for your reply!

            Yes, I see that is troublesome as you said.

            I found one more way combining field calc and pivoting measure fields in initial data source

            I attached my example, however it has big restrictions - as measure format have to be the same to use correctly aggregating functions

            I was wondering maybe there is possible way using LOD's?

            And as you said it seems that combining on dashboard will be easiest.

            Thank you!

            Zorikto

            • 3. Re: How to create calculation of YoY change in % for multiple measures in 1 table
              Shinichiro Murakami

              Hi Zorikto

               

              I'm not sure LO helps in this case. if you pivot the data, it may help.

              You still need last year, this year, and YOY as new fields.

              And you will lose the header of "year" and it becomes this year, last year.

               

              Thanks,

              Shin