topic Re: Translate sun into inputsum in QlikView

Translate sum into inputsum

nboccassini — Tue, 04 Feb 2014 09:35:54 GMT

Hi,

I have an expression of sum:

sum({$<[VOCE COSTO]={'G&A','LABOUR COST','DDA','TOT FIXED COSTS TRADING'},COMPANY-={'SPA'},ANNO={'$(VANNOCOL1)'}>} MISURA)

+sum({<COMPANY={'TOTAL G&A'}>}

aggr(sum({$<[VOCE COSTO]={'G&A'},COMPANY-={'SPA'}, ANNO={'$(VANNOCOL1)'}>} MISURA)

,[VOCE COSTO]))

+sum({<COMPANY={'TOTAL LABOUR COST'}>}

aggr(sum({$<[VOCE COSTO]={'LABOUR COST'},COMPANY-={'SPA'}, ANNO={'$(VANNOCOL1)'}>} MISURA)

,[VOCE COSTO]))

+SUM({<COMPANY={'TOT FIXED COSTS TRADING'}>}

aggr(sum({$<[VOCE COSTO]={'G&A','LABOUR COST','DDA'},COMPANY-={'SPA'}, ANNO={'$(VANNOCOL1)'}>} MISURA)

,[TOTAL_GROUP]))

cand I want to trasform it in inputsum but Changing sum in input it doesn't works.

Please help me!

Thanks

Re: Translate sun into inputsum

agilos_mla — Tue, 04 Feb 2014 09:41:52 GMT

You cannot initialize an inputfield in the layout. Firts You have to define a inputfield in the script

INPUTFIELD [MyField];

Then initiliaze the inputfield in the load.

LOAD Country,

Sales,

Sales AS [MyField]

FROM...

Then you'll be able to user inputsum ([MyField]) to override the initial Sales Amount you've loaded.

Michael

Re: Translate sun into inputsum

nboccassini — Tue, 04 Feb 2014 09:45:37 GMT

Yes, I have also defined the inputfield in the script

Re: Translate sun into inputsum

agilos_mla — Tue, 04 Feb 2014 09:51:26 GMT

OK, but you cannot use the expression :

sum({$<[VOCE COSTO]={'G&A','LABOUR COST','DDA','TOT FIXED COSTS TRADING'},COMPANY-={'SPA'},ANNO={'$(VANNOCOL1)'}>} MISURA) ....

as the initial value for the inputfield, you have to do it in the script.

I'd suggest to use two columns, one with your expression, one with the input field just beside.

Otherwise look at the KliqPlan that can manage this kind of thing

Re: Translate sum into inputsum

nboccassini — Tue, 04 Feb 2014 10:06:23 GMT

The "translation" works if I put only

sum({$<[VOCE COSTO]={'G&A','LABOUR COST','DDA','TOT FIXED COSTS TRADING'},COMPANY-={'SPA'},ANNO={'$(VANNOCOL1)'}>} MISURA)

The problem is the nex sum

+sum({<COMPANY={'TOTAL G&A'}>}

aggr(sum({$<[VOCE COSTO]={'G&A'},COMPANY-={'SPA'}, ANNO={'$(VANNOCOL1)'}>} MISURA)

,[VOCE COSTO]))

I don't know if the problem are the sum(s) or the aggr. In this case I don't know how translate this

Re: Translate sun into inputsum

agilos_mla — Tue, 04 Feb 2014 10:17:23 GMT

Aggr makes sense if you change the aggregtion function , it's not the case here sum(aggr(sum)).

I'm sure you can find a way to replace the aggr by something else.

Can you provide a sample eventually?

Michael

Re: Translate sum into inputsum

nboccassini — Tue, 04 Feb 2014 10:56:17 GMT

This is an example table data (where in MISURA there are the values and TOTAL G&A, TOTAL LABOUR COST, DDA and TOTAL FIXED COSTS TRADING not have a value in the table)

VOCE COSTO	COMPANY	MISURA
G&A	A
G&A	B
G&A	SPA
G&A	TOTAL G&A
LABOUR COST	C
LABOUR COST	D
LABOUR COST	TOTAL LABOUR COST
DDA	DDA
TOTAL FIXED COSTS TRADING	TOTAL FIXED COSTS TRADING

I want:

TOTAL G&A=A+B

TOTAL LABOUR COST=C+D

TOTAL FIXED COSTS TRADING=A+B+C+D+DDA

Re: Translate sum into inputsum

agilos_mla — Tue, 04 Feb 2014 12:05:59 GMT

I would try this :

Total G&A = sum({<COMPANY ={'A', 'B'), [VOCE COSTO]={'G&A'}>} MISURA)

Total Labour Cost = sum({<COMPANY -={'TOTAL*'), [VOCE COSTO]={'LABOUR COST'}>} MISURA)

Total Fixed Cost Trading = sum({<COMPANY -={'TOTAL*', 'TRADING'}>} MISURA)

Michael

Re: Translate sum into inputsum

nboccassini — Tue, 04 Feb 2014 14:10:12 GMT

Ok, but if I replace sum with inputsum the expression:

inputsum({$<[VOCE COSTO]={'G&A','LABOUR COST','DDA','TOT FIXED COSTS TRADING'},COMPANY -={'SPA'},ANNO={'$(VANNOCOL1)'}>} MISURA_IN)

InputSum({<COMPANY ={'A', 'B'}, [VOCE COSTO]={'G&A'}>} MISURA_IN)

doesn't works!

Re: Translate sum into inputsum

agilos_mla — Tue, 04 Feb 2014 14:21:44 GMT

I think you cannot use operator on the input sum column.

Let's try to use the set analysis UNION syntax inputsum({set1}+{set2} misura).

inputsum({$<[VOCE COSTO]={'G&A','LABOUR COST','DDA','TOT FIXED COSTS TRADING'},COMPANY -={'SPA'},ANNO={'$(VANNOCOL1)'}>+<COMPANY ={'A', 'B'}, [VOCE COSTO]={'G&A'}>} MISURA_IN)

Re: Translate sum into inputsum

nboccassini — Wed, 05 Feb 2014 09:42:44 GMT

It doesn't works!

is there a way to trasform the expression

sum({$<[VOCE COSTO]={'G&A','LABOUR COST','DDA','TOT FIXED COSTS TRADING'},COMPANY-={'SPA'},ANNO={'$(VANNOCOL1)'}>} MISURA)

sum({<COMPANY_COSTI_FISSI={'TOTAL G&A'}>}

aggr(

sum({<COMPANY_COSTI_FISSI={'TOTAL G&A'}>}/{$<[VOCE COSTO]={'G&A'},COMPANY-={'SPA'}, ANNO={'$(VANNOCOL1)'}>} MISURA)

in a unique sum??