topic Re: Calcul rules in QlikView

Calcul rules

christophebrault — Fri, 09 Dec 2011 13:35:15 GMT

I explain my situation :

i have 3 industry doing products with differents speed :

Products quantity Speed (product / hour)

Industry1 100 5

Industry2 200 10

Industry3 300 15

in a chart, i want to show the total production time for all my industry ( 1 + 2 + 3 )

In my expression, i do sum(products quantity) / Sum(Speed) BUT its not working.

Qlik view do (100 + 200 + 300) / (5 + 10 + 15)

how can i do (100*5)+ (200*10) + (300*15)

Thanks for your help

Calcul rules

Sokkorn — Fri, 09 Dec 2011 13:41:22 GMT

Try this sum(TOTAL products quantity) / Sum(TOTAL Speed)

(100*5)+ (200*10) + (300*15) ==> [Products quantity] * [Speed (product / hour)]

Regards,

Sokkorn Cheav

Re: Calcul rules

Fri, 09 Dec 2011 13:43:57 GMT

try using set analysis:

(sum({<Industry={'Industry1'}>} [products quantity])/sum({<Industry={'Industry1'}>}Speed))

(sum({<Industry={'Industry2'}>} [products quantity])/sum({<Industry={'Industry2'}>}Speed))

(sum({<Industry={'Industry3'}>} [products quantity])/sum({<Industry={'Industry3'}>}Speed))

Calcul rules

christophebrault — Fri, 09 Dec 2011 13:46:36 GMT

[Products quantity] * [Speed (product / hour)] don't work, it returns - ...

Calcul rules

christophebrault — Fri, 09 Dec 2011 13:50:39 GMT

ok, but i have a listbox to make selection between differents industries. I i use this set analysis, i can't have a dynamic selection on industries.

Calcul rules

Fri, 09 Dec 2011 13:56:24 GMT

Ok if in the chart you use industry as a dimension then sum(products quantity) / Sum(Speed) should work because it is only applying it to each Industry at a time.

I am not sure if it will work but you could try using an IF THEN statement instead:

(sum(If(Industry='Industry1', [products quantity],0))/sum(if(Industry='Industry1',Speed,0)))

(sum(If(Industry='Industry2', [products quantity],0))/sum(if(Industry='Industry2',Speed,0)))

(sum(If(Industry='Industry3', [products quantity],0))/sum(if(Industry='Industry3',Speed,0)))

Re: Calcul rules

johnw — Sat, 10 Dec 2011 01:15:56 GMT

If the chart you show is your raw data:

sum("Products quantity" * "Speed (product/hour)")

If your products quantity and speed columns are sums by industry in the chart, and you're trying to get a total line:

sum(aggr(sum("Products quantity")*sum("Speed (product/hour)"),"Industry"))

Or are you trying to do a weighted average?

sum("Products quantity" * "Speed (product/hour)")/sum("Speed (product/hour)")

sum(aggr(sum("Products quantity")*sum("Speed (product/hour)"),"Industry"))/sum("Speed (product/hour)")