topic Re: Multiply figures in a pivot - column in QlikView

Multiply figures in a pivot - column

Sat, 12 Sep 2015 06:21:23 GMT

Hello All,

Is that possible to multiply key figures in a column and return that in a variable like this sample:

Year Country City populGrowth

2014 USA NY 0,12

2014 USA MC 0,045

2014 USA MI 0,21

2014 CAN OTW 0,44

2014 CAN CAL 0,22

and so on.

I try to multiply the figures based on year and country:

2014/USA: 0,12*0,045*0,21

2014/CAN: 0,44*0,22

Thank you for any clue?

Fabian

Re: Multiply figures in a pivot - column

Gysbert_Wassenaar — Sat, 12 Sep 2015 10:57:40 GMT

Perhaps like this:

T1:
LOAD * INLINE [
    Year, Country, City, populGrowth
    2014, USA, NY, 0.12
    2014, USA, MC, 0.045
    2014, USA, MI, 0.21
    2014, CAN, OTW, 0.44
    2014, CAN, CAL, 0.22
];

T2:
LOAD Year & '/' & Country as [Year/Country], Evaluate(concat(populGrowth,'*')) as Factor
Resident T1
Group By Year & '/' & Country;

Re: Multiply figures in a pivot - column

Sat, 12 Sep 2015 11:36:11 GMT

Hi Gysbert,

Thank you for help. I would solve this action in pivot-table. Is there any way in Qlikview like function evaluate(concate(attr,'*')) like your script? I did not find it.

Thank you for your help.

Re: Multiply figures in a pivot - column

Gysbert_Wassenaar — Sat, 12 Sep 2015 11:40:37 GMT

Try exp(sum(log(populGrowth)))

Re: Multiply figures in a pivot - column

Sat, 12 Sep 2015 11:51:48 GMT

I tried that and the result is the same like this synthax:

aggr(sum(populGrowth),Year,Country)

2014/CAN : 1,00

2014/USA: 45,0

Do you know why?

Re: Multiply figures in a pivot - column

Gysbert_Wassenaar — Sat, 12 Sep 2015 12:00:11 GMT

No idea. It works here. See attached example.

Re: Multiply figures in a pivot - column

Sat, 12 Sep 2015 12:42:17 GMT

Many thanks for your help. I found the error. I droped whole numeric and time format sets, before I started with scripting. After inserting of that your synthax it works.

Thank you again.

Best regards