6 Replies Latest reply: Nov 23, 2016 2:35 AM by Lukas B�denbender RSS

    compare actual vs budget

    Lukas B�denbender

      hi,

      I've got the following pivot table

      VBEL.png

      Umsatz Plan (sales budget), Umsatz Ist (sales actual) etc. are expressions with VBELN as the dimension.

      Is it possible to make it look like this

       

      VBELN7000001369
      budgetactual
      sales
      engineering
      material

       

      thanks in advance!

        • Re: compare actual vs budget
          popescu cosmina

          Hi,

           

          Can you upload  a sample of data, for example for 2 VBELN. I need to see the structure of the data. Inn the pivot table i don't understand how the data are stored in tables.

           

          I have attached with the Sample i used. Idee is to make a new field, without containing Ist and Plan, after that to make a Flag if it is Plan or Actual. Then in Pivot is simple. Please see attached. All depends on how you store the data.

           

          Best regads,

          Cosmina

          • Re: compare actual vs budget
            popescu cosmina

            Hi,

             

            The only Solution I see is to add in the KVBI and CKSI Actual and Bugetat for DB lb Budget and Db lb Budget which is simple: I.e for Actual - DB lb Budget

             

             

            //load all data

            NoConcatenate

            Kvbi_TEMP:

            LOAD *

            Resident KVBI;

             

             

            LEFT JOIN(Kvbi_TEMP)

            LOAD * Resident Kosternarten;

             

             

             

             

            //load Actual for Umsotzerlose

            temp_1:

            LOAD *

            Resident Kvbi_TEMP

            where Kostenartenbezeichnung='Umsotzerlose';

             

             

            NoConcatenate

            temp_2:

            LOAD VBELN, -SUM(Actual) as Actual

            Resident temp_1

            group by  VBELN;

            drop table temp_1;

             

             

            //Load Actual for Konstruction ...sonst HK

            NoConcatenate

            temp_3:

            LOAD *

            Resident Kvbi_TEMP

            where match(Kostenartenbezeichnung,'Konstruction','Material','Fetihung','Mortage','sonst HK');

             

             

            NoConcatenate

            temp_4:

            LOAD VBELN, SUM(Actual) as Actual

            Resident temp_3

            group by  VBELN;

             

             

            DROP TABLE temp_3;

             

             

            //CALCULATE THE ACTUAL FOR DB lb Budget

            Concatenate(temp_4)

            LOAD * Resident temp_2;

             

             

            NoConcatenate

            temp_5:

            LOAD VBELN, SUM(Actual) as Actual, 1 as KSTAR

            RESIDENT temp_4

            Group BY VBELN;

             

             

            DROP TABLE temp_4;

             

             

            //ad DB lb Budget

             

             

            CONCATENATE(KVBI)

            LOAD * Resident temp_5;

             

             

            DROP TABLE temp_5;

             

             

            Concatenate(Kostenarten)

            LOAD * INLINE [KSTAR, Kostenartenzeichnung

            1, Db lb Budget];

             

            This is only the logic, please be carreful on speling. Then you will have Kostenartenbezeichnung and VBELN as Dimension and Actual and Bugeted as expression in Pivot Table.

             

            Best regards,

            Cosmina

            • Re: compare actual vs budget
              Adam Davies

              Hi Lukas,

               

              In this case can you simply outer join your Budget table to your existing actuals?

              Sorry hard to picture it in my head without trying it out! but change your 'database' tab to be the code below and ditch the CKIS tab

              
              
              
              LOAD Kostenart
              
              as KSTAR, 
              
              
                   Verkaufsb.
              
              as VBELN, 
              
              
                   [Wert/KWähr]
              as Actual
              
              
              
              
              
              FROM
              [C:\Users\buedenbenderl\Desktop\KVBI neuer Versuch nur eine Auftrgsnummer\für Community\Fact\KVBI 7000001369.xlsx]
              (ooxml, embedded labels, table is KVBI);
              
              
              Concatenate
              
              
              
              
              
              LOAD Kostenart
              
              as KSTAR, 
              
              
                   Verkaufsb.
              
              as VBELN, 
              
              
                   [Wert/KWähr]
              as Actual
              
              
              
              FROM
              [C:\Users\buedenbenderl\Desktop\KVBI neuer Versuch nur eine Auftrgsnummer\für Community\Fact\KVBI 7000000010.xlsx]
              (ooxml, embedded labels, table is Tabelle1);
              
              
              OUTER JOIN 
              LOAD  
              
              
              
              ApplyMap ('VBAP',KALNR,'Unknown')
              
              as VBELN,
              
              
              
                   KSTAR, 
              
              
              
                   WERTB
              
              
              as Budget
              
              
              
              FROM
              [C:\Users\buedenbenderl\Desktop\KVBI neuer Versuch nur eine Auftrgsnummer\CKIS 7000001369.xlsx]
              (ooxml, embedded labels, header is 1 lines, table is CKIS);
              
              • Re: compare actual vs budget
                Lukas B�denbender

                Thank you both,

                but it was way simpler than that. Just changed the expressions to sum(actual) and sum(budget) and added the dimension cost types.   a bit pivoting and it was done.

                pivot.png