39 Replies Latest reply: Jul 18, 2016 11:37 AM by Tamil Nagaraj RSS

    How to Divide by 2 Variables

    Lauren Mills

      I have two variables.... They are both counts of certain criteria. I want to be able to write an expression to divide them. When I write it.... I get a blank line. I know my two variables are working correctly. I put them both in a KPI and the numbers populate.

       

       

      I wrote my expression like so.... $(vCountPOScode1) / $(v%POScode1)

       

      I'm not sure why this isn't working. Please advise.

        • Re: How to Divide by 2 Variables
          Sunny Talwar

          How about this:

           

          vCountPOScode1/v%POScode1

            • Re: How to Divide by 2 Variables
              Lauren Mills

              Nope. That didn't work. Any other ideas or thoughts? I really need to solve this for work today. Any help is huge. I appreciate it!

                • Re: How to Divide by 2 Variables
                  Sunny Talwar

                  How about this:

                   

                  $(=vCountPOScode1)/$(=v%POScode1)

                   

                  and also have you used = sign before your variable definition? Sometimes adding/removing it helps in QlikView. My guess is that it will help in Qlik Sense as well

                    • Re: How to Divide by 2 Variables
                      Lauren Mills

                      That didn't work either. I tried with and without the = sign. Would it help at all seeing may variable expressions?

                        • Re: How to Divide by 2 Variables
                          Sunny Talwar

                          May be , would you be able to share?

                            • Re: How to Divide by 2 Variables
                              Lauren Mills

                              v%POScode1:

                               

                              count({<[Position Code]={12,42}>}SID) + count({<[Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]) + count({<[Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]) + count({<[Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID])

                               

                              vCountPOScode1:

                               

                              Count({$<[LEVEL 0 COMPLETION]={"Y"}, [Position Code]={12,42}>}SID) + Count({$<[Sales Manager$.LEVEL 0 COMPLETION]={"Y"}, [Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]) + Count({$<[Service Advisor$.LEVEL 0 COMPLETION]={"Y"}, [Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]) + Count({$<[Service Manager$.LEVEL 0 COMPLETION]={"Y"}, [Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID])

                                • Re: How to Divide by 2 Variables
                                  Sunny Talwar

                                  How about try these as your variables:

                                   

                                  RangeSum(

                                  Count({<[Position Code]={12,42}>}SID),

                                  Count({<[Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]),

                                  Count({<[Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]),

                                  Count({<[Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID]))


                                  RangeSum(

                                  Count({$<[LEVEL 0 COMPLETION]={"Y"}, [Position Code]={12,42}>}SID),

                                  Count({$<[Sales Manager$.LEVEL 0 COMPLETION]={"Y"}, [Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]),

                                  Count({$<[Service Advisor$.LEVEL 0 COMPLETION]={"Y"}, [Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]),

                                  Count({$<[Service Manager$.LEVEL 0 COMPLETION]={"Y"}, [Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID]))

                                  • Re: How to Divide by 2 Variables
                                    Alex Pan

                                    Is it possible your v%POScode1 is 0?

                                    • Re: How to Divide by 2 Variables
                                      Tamil Nagaraj

                                      Try this in textbox and check whether you are getting result or not,

                                       

                                      = ( Count({<[Position Code]={12,42}>}SID) + count({<[Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]) + count({<[Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]) + count({<[Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID])

                                      /

                                      Count({$<[LEVEL 0 COMPLETION]={"Y"}, [Position Code]={12,42}>}SID) + Count({$<[Sales Manager$.LEVEL 0 COMPLETION]={"Y"}, [Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]) + Count({$<[Service Advisor$.LEVEL 0 COMPLETION]={"Y"}, [Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]) + Count({$<[Service Manager$.LEVEL 0 COMPLETION]={"Y"}, [Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID])

                                      )

                                        • Re: How to Divide by 2 Variables
                                          Lauren Mills

                                          I didn't get any results with that.

                                            • Re: How to Divide by 2 Variables
                                              Tamil Nagaraj

                                              Seems one (or both) of your expression (s) returns nothing. Check the below expressions separately in text and see what happens.

                                               

                                              =Count({<[Position Code]={12,42}>}SID) + count({<[Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]) + count({<[Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]) + count({<[Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID])

                                               

                                              =Count({$<[LEVEL 0 COMPLETION]={"Y"}, [Position Code]={12,42}>}SID) + Count({$<[Sales Manager$.LEVEL 0 COMPLETION]={"Y"}, [Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]) + Count({$<[Service Advisor$.LEVEL 0 COMPLETION]={"Y"}, [Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]) + Count({$<[Service Manager$.LEVEL 0 COMPLETION]={"Y"}, [Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID])

                                                • Re: How to Divide by 2 Variables
                                                  Lauren Mills

                                                  Yes, Those both give me the values I've had all along. They are returning numbers to me. I just need them to divide by each other -  which is where I don't receive a value.

                                                    • Re: How to Divide by 2 Variables
                                                      Tamil Nagaraj

                                                      Fine. Change your variable like below,

                                                       

                                                      v%POScode1:

                                                       

                                                      ( count({<[Position Code]={12,42}>}SID) + count({<[Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]) + count({<[Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]) + count({<[Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID]) )

                                                       

                                                      vCountPOScode1:

                                                       

                                                      ( Count({$<[LEVEL 0 COMPLETION]={"Y"}, [Position Code]={12,42}>}SID) + Count({$<[Sales Manager$.LEVEL 0 COMPLETION]={"Y"}, [Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]) + Count({$<[Service Advisor$.LEVEL 0 COMPLETION]={"Y"}, [Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]) + Count({$<[Service Manager$.LEVEL 0 COMPLETION]={"Y"}, [Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID]) )

                                                       

                                                      And try this expression in textbox

                                                       

                                                      $(vCountPOScode1) / $(v%POScode1)

                                                        • Re: How to Divide by 2 Variables
                                                          Lauren Mills

                                                          The expressions for the variables work... but the / by variables expression still returns no value. I don't understand what's missing. I have two numbers, which can be divided to give me a value (%) but why won't Qlik Sense recognize it?

                                                            • Re: How to Divide by 2 Variables
                                                              Tamil Nagaraj

                                                              Lauren,

                                                               

                                                              Copy below expressions (Exactly what I posted) and check

                                                               

                                                              v%POScode1:

                                                               

                                                              =( count({<[Position Code]={12,42}>}SID) + count({<[Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]) + count({<[Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]) + count({<[Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID]) )

                                                               

                                                              vCountPOScode1:

                                                               

                                                              =( Count({$<[LEVEL 0 COMPLETION]={"Y"}, [Position Code]={12,42}>}SID) + Count({$<[Sales Manager$.LEVEL 0 COMPLETION]={"Y"}, [Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]) + Count({$<[Service Advisor$.LEVEL 0 COMPLETION]={"Y"}, [Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]) + Count({$<[Service Manager$.LEVEL 0 COMPLETION]={"Y"}, [Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID]))

                                                               

                                                              And try this expression in textbox

                                                               

                                                              $(vCountPOScode1) / $(v%POScode1)

                                                    • Re: How to Divide by 2 Variables
                                                      Lauren Mills

                                                      Hold on... I added the '=' and I did get a result.

                                                       

                                                        • Re: How to Divide by 2 Variables
                                                          Lauren Mills

                                                          Not the number I'm looking for though...

                                                           

                                                           

                                                          I'm looking for 44%

                                                            • Re: How to Divide by 2 Variables
                                                              Sunny Talwar

                                                              Try these with the = sign

                                                               

                                                              =RangeSum(

                                                              Count({<[Position Code]={12,42}>}SID),

                                                              Count({<[Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]),

                                                              Count({<[Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]),

                                                              Count({<[Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID]))


                                                              =RangeSum(

                                                              Count({$<[LEVEL 0 COMPLETION]={"Y"}, [Position Code]={12,42}>}SID),

                                                              Count({$<[Sales Manager$.LEVEL 0 COMPLETION]={"Y"}, [Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]),

                                                              Count({$<[Service Advisor$.LEVEL 0 COMPLETION]={"Y"}, [Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]),

                                                              Count({$<[Service Manager$.LEVEL 0 COMPLETION]={"Y"}, [Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID]))

                                                    • Re: How to Divide by 2 Variables
                                                      Vinay kumar Bangari

                                                      Lauren, By seeing the chart i can say that this is from Qlik Sense. you would get better answers if it is posted directly in qlik sense

                                              • Re: How to Divide by 2 Variables
                                                Lauren Mills

                                                Don't give up on me guys. I really need help to figure this out

                                                  • Re: How to Divide by 2 Variables
                                                    Sunny Talwar

                                                    Just out of curiosity, have you tried this

                                                     

                                                    vCountPOScode1/v%POScode1


                                                    with and without ('=')


                                                    =RangeSum(

                                                    Count({<[Position Code]={12,42}>}SID),

                                                    Count({<[Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]),

                                                    Count({<[Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]),

                                                    Count({<[Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID]))


                                                    =RangeSum(

                                                    Count({$<[LEVEL 0 COMPLETION]={"Y"}, [Position Code]={12,42}>}SID),

                                                    Count({$<[Sales Manager$.LEVEL 0 COMPLETION]={"Y"}, [Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]),

                                                    Count({$<[Service Advisor$.LEVEL 0 COMPLETION]={"Y"}, [Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]),

                                                    Count({$<[Service Manager$.LEVEL 0 COMPLETION]={"Y"}, [Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID]))

                                                      • Re: How to Divide by 2 Variables
                                                        Lauren Mills

                                                        Yes. I have edited the variables to the stated about and when I put 'vCountPOScode1/v%POScode1' as the expression in the gauge, it doesn't like the field names so I change it to '$(vCountPOScode1/v%POScode1)' and it doesn't give me any numbers.

                                                         

                                                        Please advise if I am not seeing something correctly. I really appreciate you not giving up on me!

                                                          • Re: How to Divide by 2 Variables
                                                            Sunny Talwar

                                                            Do you see anything in the KPI object when you use variation of your expression in them?

                                                              • Re: How to Divide by 2 Variables
                                                                Lauren Mills

                                                                 

                                                                No, I get the same numbers across the board with different variations.

                                                                  • Re: How to Divide by 2 Variables
                                                                    Sunny Talwar

                                                                    I meant division of the two variables in KPI object rather than gauge (just for testing)

                                                                      • Re: How to Divide by 2 Variables
                                                                        Lauren Mills

                                                                        I just tried - it returned a line.

                                                                         

                                                                          • Re: How to Divide by 2 Variables
                                                                            Sunny Talwar

                                                                            I give up ... I have no idea what might be the issue. Would it be possible to replicate the issue in a sample qvf?

                                                                              • Re: How to Divide by 2 Variables
                                                                                Lauren Mills

                                                                                However.... I put this in the gauage -

                                                                                 

                                                                                RangeSum(

                                                                                Count({$<[LEVEL 0 COMPLETION]={"Y"}, [Position Code]={12,42}>}SID),

                                                                                Count({$<[Sales Manager$.LEVEL 0 COMPLETION]={"Y"}, [Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]),

                                                                                Count({$<[Service Advisor$.LEVEL 0 COMPLETION]={"Y"}, [Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]),

                                                                                Count({$<[Service Manager$.LEVEL 0 COMPLETION]={"Y"}, [Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID]) ) / RangeSum(

                                                                                Count({<[Position Code]={12,42}>}SID),

                                                                                Count({<[Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]),

                                                                                Count({<[Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]),

                                                                                Count({<[Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID]))

                                                                                 

                                                                                 

                                                                                and I got my 44%. Why does this work by itself and not in my variables? I will settle. I will just use this without using my variables to get the number I need.

                                                                                  • Re: How to Divide by 2 Variables
                                                                                    Tamil Nagaraj

                                                                                    I would like to know whether below one is working or not. I did a test and returns value.


                                                                                    v%POScode1:

                                                                                     

                                                                                    =( count({<[Position Code]={12,42}>}SID) + count({<[Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]) + count({<[Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]) + count({<[Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID]) )

                                                                                     

                                                                                    vCountPOScode1:

                                                                                     

                                                                                    =( Count({$<[LEVEL 0 COMPLETION]={"Y"}, [Position Code]={12,42}>}SID) + Count({$<[Sales Manager$.LEVEL 0 COMPLETION]={"Y"}, [Sales Manager$.Position Code]={04,41}>}[Sales Manager$.SID]) + Count({$<[Service Advisor$.LEVEL 0 COMPLETION]={"Y"}, [Service Advisor$.Position Code]={13,14,27,40}>}[Service Advisor$.SID]) + Count({$<[Service Manager$.LEVEL 0 COMPLETION]={"Y"}, [Service Manager$.Position Code]={08,09,17,32,33,35}>}[Service Manager$.SID]))

                                                                                     

                                                                                    And try this expression in textbox

                                                                                     

                                                                                    $(vCountPOScode1) / $(v%POScode1)

                                                                    • Re: How to Divide by 2 Variables
                                                                      Vinay kumar Bangari

                                                                      phaneendra.kunche can you please help on this.

                                                                    • Re: How to Divide by 2 Variables
                                                                      Tamil Nagaraj

                                                                      I would like to know whether my last post working or not. I did a test and returns value.