6 Replies Latest reply: Dec 9, 2011 8:15 PM by John Witherspoon RSS

    Calcul rules

    Christophe Brault

      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

        • Calcul rules
          Sokkorn Cheav

          Hi

           

          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

            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
                Christophe Brault

                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

                    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
                        John Witherspoon

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