Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
nboccassini
Contributor

Translate sum into inputsum

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

Tags (3)
10 Replies
agilos_mla
Contributor III

Re: Translate sun into inputsum

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

nboccassini
Contributor

Re: Translate sun into inputsum

Yes, I have also defined the inputfield in the script

agilos_mla
Contributor III

Re: Translate sun into inputsum

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


nboccassini
Contributor

Re: Translate sum into inputsum

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



agilos_mla
Contributor III

Re: Translate sun into inputsum

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

nboccassini
Contributor

Re: Translate sum into inputsum

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 COSTOCOMPANYMISURA
G&AA
G&AB
G&ASPA
G&ATOTAL G&A
LABOUR COSTC
LABOUR COSTD
LABOUR COSTTOTAL LABOUR COST
DDADDA
TOTAL FIXED COSTS TRADINGTOTAL FIXED COSTS TRADING

I want:

TOTAL G&A=A+B

TOTAL LABOUR COST=C+D

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

agilos_mla
Contributor III

Re: Translate sum into inputsum

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

nboccassini
Contributor

Re: Translate sum into inputsum

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!

agilos_mla
Contributor III

Re: Translate sum into inputsum

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)

Community Browser