Translate sum into inputsum

[nboccassini]

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

[agilos_mla]

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]

Yes, I have also defined the inputfield in the script

[agilos_mla]

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]

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]

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]

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

[agilos_mla]

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]

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]

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)