Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

christophebrault
Valued Contributor

Calcul rules

Hi

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Calcul rules

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)")

6 Replies
Sokkorn
Honored Contributor

Calcul rules

Hi

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

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

Regards,

Sokkorn Cheav

Not applicable

Re: Calcul rules

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))

christophebrault
Valued Contributor

Calcul rules

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

christophebrault
Valued Contributor

Calcul rules

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.

Not applicable

Calcul rules

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)))

MVP
MVP

Re: Calcul rules

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)")

Community Browser