Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nboccassini
Partner - Creator
Partner - Creator

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

10 Replies
agilos_mla
Partner - Creator III
Partner - Creator III

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
Partner - Creator
Partner - Creator
Author

Yes, I have also defined the inputfield in the script

agilos_mla
Partner - Creator III
Partner - Creator III

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
Partner - Creator
Partner - Creator
Author

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
Partner - Creator III
Partner - Creator III

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
Partner - Creator
Partner - Creator
Author

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
Partner - Creator III
Partner - Creator III

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
Partner - Creator
Partner - Creator
Author

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
Partner - Creator III
Partner - Creator III

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)