15 Replies Latest reply: Jan 9, 2011 7:59 AM by isaac li RSS

    How to calculate rate

    isaac li

      Hi All,

      I have one table:

      load * Inline
      [OrderId,SubOrderId
      1,1
      2,2
      3
      4
      ]

      I want to calculate how many records have sub order, in this case, it is 2/4 .

      Could you help me out?

       

      Thanks.

        • How to calculate rate
          Miguel Angel Baeyens de Arce

          Hello Isaac,

          I'd create a new flag field and use it in the expression:

           

          LOAD *, If(Len(SubOrderId) > 0, 1, 0) AS FlagHasSubOrderRESIDENT Table; DROP TABLE Table; // To avoid unwanted syn keys


          Expression should look like

           

          Count({< FlagHasSubOrder = {1} >} OrderId) / Count({1} OrderId)


          Hope this helps

            • How to calculate rate
              isaac li

              Hi,

              Thanks for your quick reply.

              But is there any other method that just uses expression directly?

               

              Thanks.

                • How to calculate rate
                  Miguel Angel Baeyens de Arce

                  Since you are loading form an inline table, this expression should work

                   

                  =Count({< SubOrderId = {"=IsNum(SubOrderId)"} >} SubOrderId) / Count(OrderId)


                  Depending on how your data source and driver deals with nulls, that set analysis may not work.

                  Hope this helps.

                  • How to calculate rate
                    isaac li

                    Hi,

                    I mean, if there are selection have been choosen, this rate will be changed.

                    I want to it keep 50% permanent. I know I need to add 1 not $ into this expression, but how to do it?

                    =Count({< SubOrderId = { "=IsNum(SubOrderId)" } >} SubOrderId)

                     

                    Thanks.

                      • How to calculate rate
                        Miguel Angel Baeyens de Arce

                        Hello Isaac,

                        I don't think it's possible because of the modifier with an explicit call to a function (IsNum()). I'd rather use a detached gauge chart (object properties, detach).

                        Hope this helps.

                          • How to calculate rate
                            isaac li

                            Hi,

                            I am confused by the set analysis, when shall I use $ or not.

                            For example:

                            If I want to get the Max month Amount based on the selection:

                            It works:

                            =sum({$<MonthOfYear={"=Max(MonthOfYear)-1"}>}Amount)

                            But I want to get the amount of the month before the max month, I use

                            =sum({$<MonthOfYear={"=Max(MonthOfYear)-1"}>}Amount)

                            But it does not work.

                            But this expression works:

                            =sum({$<MonthOfYear={$(=Max(MonthOfYear)-1)}>}Amount)

                             

                            So what's wrong with them?

                             

                             

                             

                              • How to calculate rate
                                Miguel Angel Baeyens de Arce

                                Hello Isaac,

                                There are two things here:

                                In Set Analysis, "$" as a part of the set or preceding the set modifier means "the current state of selections", that is the filters specified in Set Analysis will be done on the basis of the already done. You don't need to set this.

                                 

                                Sum({$< Country = {'ES'} >} Amount) or Sum({< Country = {'ES'} >} Amount)


                                Both will return the same results.

                                Don't mix that up with the dollar expansion for functions or variables that may or may not be used in Set Analysis. As a general rule, every time you want to evaluate a variable (not only show its content, but evaluate the content and return it result) you will need to use the $(). Example:

                                 

                                SET vSum = 1 + 1;// Now vSum will return the string "1 + 1"// But $(vSum) will return "2"


                                In your case, $(=Max()) is evaluating the function and returning its result, and that's why it's working.

                                Hope this helps.

                                • How to calculate rate
                                  isaac li

                                  Thanks,

                                  But what's the meaning of the bold expression: =sum({$<MonthOfYear={"=Max(MonthOfYear)"}>}Amount)?

                                  Is it a set or function? If it is a function, why does not need to use $ and just use double quote?

                                   

                                  I know the above set works, but if I want to use this form that does not use $ to get the amount that is before the max month:

                                  =sum({$<MonthOfYear={ "=Max(MonthOfYear)-1"}>}Amount)

                                   

                                  It does not work, it the responses the same result with =sum({$<MonthOfYear={"=Max(MonthOfYear)"}>}Amount).

                                   

                                  I am sunk!

                                   

                                   

                                   

                                  Thanks sir.

                                   

                                  Isaac Li

                                   

                                   

                                    • How to calculate rate
                                      Miguel Angel Baeyens de Arce

                                      Hello Isaac,

                                      The bold part is a function within a set analysis modifier. It's used for example when you need to call the same field you are using in the field.

                                       

                                      Sum({< CompanyID = {"=Left(CompanyID, 2) = 'ES'"} >} Amount)


                                      That will sum all amounts where company starts in "ES".

                                       

                                      Sum({< CompanyID = {"=Sum(Expenses) > 1000"} >} Amount)


                                      That will sum all amounts for companies that have expenses greater than 1000.

                                      But if you want to get the value itself (in your case, the month previous to the last month loaded) that is a result of an evaluation to be passed on to the filter to select those values, then the proper syntax to use a function within set analysis is using dollar expansion:

                                       

                                      Sum({< Date = {'>=$(=YearStart(Today()))'} >} Amount)


                                      Hope this helps.

                                        • How to calculate rate
                                          isaac li

                                          Hi,

                                          I get it, but why I can not use "-1" in the set analysis?

                                          sum({$<MonthOfYear={ "=Max(MonthOfYear)-1"}>}Amount)

                                           

                                           

                                            • How to calculate rate
                                              Miguel Angel Baeyens de Arce

                                              As I told above, that needs to be evaluated (it's not a result by itself), so that's why you need to do

                                               

                                              sum({$<MonthOfYear={ '$(=Max(MonthOfYear)-1)'}>}Amount)


                                              "1" is a numeric value, it doesn't need to be evaluated, and as such, it may be used as a field filter selection in set analysis or in the script.

                                              "1 + 1" its not a numeric value, unless it's evaluated. It may be a string that for any reason you want to keep that way. Note that there are not data types as such in QlikView, rather than numeric and literal representations of any value, and that's why you need functions.

                                              It's a question of syntax, as it happens in any other programming languange. Functions usually need to be evaluated.

                                              Hope this helps.

                              • How to calculate rate

                                Hi Issaac,

                                Try this one

                                count(Col_Name)

                                Hope its useful