topic Re: compare actual vs budget in QlikView

compare actual vs budget

Anonymous — Wed, 09 Nov 2016 14:08:15 GMT

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!

Re: compare actual vs budget

Anonymous — Thu, 10 Nov 2016 08:18:39 GMT

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

Anonymous — Fri, 11 Nov 2016 08:00:15 GMT

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

Re: compare actual vs budget

Anonymous — Fri, 11 Nov 2016 15:10:44 GMT

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

Anonymous — Tue, 22 Nov 2016 12:45:40 GMT

Hi Cosima,

thanks a lot. I will try to adjust the script to your suggestion.

Best regards

Lukas

Re: compare actual vs budget

adamdavi3s — Tue, 22 Nov 2016 13:02:42 GMT

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);

Re: compare actual vs budget

Anonymous — Wed, 23 Nov 2016 07:35:43 GMT

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.

LOAD Kostenart		as KSTAR,
Verkaufsb.		as VBELN,
[Wert/KWähr]	as Actual