topic Re: How to do the calculations between the different dimensions? in QlikView

How to do the calculations between the different dimensions?

Thu, 26 Nov 2015 18:03:20 GMT

Hi all, here above the table. I need to divide per line the quantity of dimension "gp_pr" by the volume of dimension "gp_co".

For now, I can only get the result per plant , which means the global quantity of "gp_pr" and "gp_co".

Please see below the current expressions:

I've attached here the simple qvw and 2 excel files (" Test print" is the database of "quantity" and "mapping" is the result i want to show in report)

Ce message a été modifié par : Q H

Re: How to do the calculations between the different dimensions?

swuehl — Thu, 26 Nov 2015 21:28:12 GMT

What are dimensions _Print_CO_... resp. _Print_WC ? Fields or calculated dimensions?

If they are fields, you can maybe use the TOTAL qualifier with an appropriate fields list:

=Sum(TOTAL<Plant, _Print_CO_... > Volumes)

resp.

=Sum(TOTAL<Plant, _Print_WC > Volumes)

Not sure if this is what you are looking for. Could you upload a small sample QVW and your desired results as excel or similar?

Re: How to do the calculations between the different dimensions?

Tue, 01 Dec 2015 13:40:48 GMT

hi swuehl,

"_Print_CO" and "_Print_WC" come from the same field.

in the field, the éléments present as "32ERF105", "33ERP105"...

To do the caluculation per plant, e.g. volume of "32XXXxxx" divided by the volume of "33XXXxxx", I added a mapping in the script area in order to regroup the elements :

LOAD * INLINE [

_plant, _print_c1, _print_lin1, _print_c2, _print_lin2,

ERF, 33XXXxxx, P-2C, 32XXXxxx, PCO-2C

CUX, 33XXXxxx, P-2C, 32XXXxxx, PCO-2C

... ...

]

Maybe the mapping is not correct?

Re: How to do the calculations between the different dimensions?

Wed, 02 Dec 2015 17:45:26 GMT

Hi, I've attached a simpe qvw and 2 excel files : " Test print" is the database and " mapping" is the table i what to show in the report. In the qvw you could see the calculations are done per plant instead of per line. Thanks to let me know if it's not clear.

Re: How to do the calculations between the different dimensions?

swuehl — Wed, 02 Dec 2015 19:18:08 GMT

Sorry, still unclear to me how you want to do the calculations. Could you explain this in more detail, e.g. for the first line

plant code	wc1	gp_pr	gp_co	quantity gp_pr	quantity gp_co
A	33CUX345	PRINT-2C	PRCHGOV-2C	25	95	0

Re: How to do the calculations between the different dimensions?

swuehl — Wed, 02 Dec 2015 19:51:26 GMT

This replicates your Mapping table for the two quantitiy columns, though I have no idea if this is correct.

plant code	wc1	gp_pr	gp_co	sum(if(wc1= wc and gp_pr = gp,quantity))	sum(if(gp_co = gp and mid(wc,3) = mid(wc1,3) ,quantity))	Column(1)/Column(2)
W	33WEI042	PRINT-2C	PRCHGOV-2C	82,0	65,0	1,2615384615385
	33WEI061	PRINT-4C	PRCHGOV-4C	64,0	74,0	0,86486486486486
	33WEI062	PRINT-6C	PRCHGOV-6C	36,0	21,0	1,7142857142857
	33WEI063	PRINT-6C	PRCHGOV-6C	54,0	57,0	0,94736842105263
	33WEI064	PRINT-6C	PRCHGOV-6C	43,0	22,0	1,9545454545455
E	33ERF105	PRINT-2C	PRCHGOV-2C	28,0	52,0	0,53846153846154
E	33ERF108	PRINT-1CFOV	PRCHGOV-1CFOV	66,0	44,0	1,5
A	33CUX345	PRINT-2C	PRCHGOV-2C	25,0	95,0	0,26315789473684
A	33CUX363	PRINT-4C	PRCHGOV-4C	27,0	86,0	0,31395348837209