Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi,
I've got the following pivot table
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
VBELN | 7000001369 | |
---|---|---|
budget | actual | |
sales | ||
engineering | ||
material | ||
… |
thanks in advance!
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.
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
Hi Cosima,
thanks a lot for your response. See the sample file attached . I've made some comments in the script.
I hope this will help.
Thanks in advance
Lukas
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
Hi Cosima,
thanks a lot. I will try to adjust the script to your suggestion.
Best regards
Lukas
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
(ooxml, embedded labels, table is KVBI);
Concatenate
LOAD Kostenart | as KSTAR, | |
Verkaufsb. | as VBELN, | |
[Wert/KWähr] | as Actual |
FROM
(ooxml, embedded labels, table is Tabelle1);
OUTER JOIN
LOAD
ApplyMap ('VBAP',KALNR,'Unknown') | as VBELN, |
KSTAR,
WERTB | as Budget |
FROM
(ooxml, embedded labels, header is 1 lines, table is CKIS);
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.