5 Replies Latest reply: Feb 22, 2017 7:34 AM by Sunny Talwar RSS

    Pivottable compare Columns

    Henry Tran

      Hi all,

       

      I am struggling with a comparison between columns in a pivot table.

      1.JPG

      In the columns I have years as well as months. What I want to do is compare the values from one year to the year before.

      Right now I am using "before" but it only works with the years. When I open the years up into months it won't compare the month of 2017 with the same month of 2016.

       

      I tried something like this

       

      if(sum ( {< DF_BRUTTO_NETTO = {'Anwesend'} > }BU_BU_WERT_SUM) > 0,

       

       

      sum ( {< DF_BRUTTO_NETTO = {'Anwesend'}, Flag = {'2017'} > }BU_BU_WERT_SUM)

       

      /

       

      sum ( {< DF_BRUTTO_NETTO = {'Anwesend'}, Flag = {'2016'} > }BU_BU_WERT_SUM) -1)

       

      but it's not showing me the correct results.

       

      I have attached an example.

       

      Thank you for your help.

        • Re: Pivottable compare Columns
          Sunny Talwar

          I see that you have AsOfTable in your app, why don't you use that in your chart? If you don't want to use it, then you can use an expression like this (will need to be fine tuned based on your exact requirement)

           

          If(SecondaryDimensionality() = 1,

          Sum( {< DF_BRUTTO_NETTO = {'Anwesend'} >} BU_BU_WERT_SUM) /

          after(Sum( {< DF_BRUTTO_NETTO = {'Anwesend'} >} BU_BU_WERT_SUM))-1,

          If(SecondaryDimensionality() = 2,

          Aggr(Sum( {< DF_BRUTTO_NETTO = {'Anwesend'} >} BU_BU_WERT_SUM) /

          Below(Sum( {< DF_BRUTTO_NETTO = {'Anwesend'} >} BU_BU_WERT_SUM))-1, DF_UNTERNEHMUNG, DD_MONAT, DD_JAHR)))

            • Re: Pivottable compare Columns
              Henry Tran

              Hi Sunny,

               

              nice to have your help again

               

              I have some questions to your solution.

              There are some issues with it.

               

              The color expressions don't seem to work anymore if I open it up into months.

              Capture.JPG

               

              As you can see there are negative numbers with a green color?

              Also when I open it up into months the %-differance of totals is disappearing (they are replaced with "-").

               

              Could you show me a solution with the AsOfTable-table? (You showed me the function of it about 2 months back)

               

              Thank you!