5 Replies Latest reply: Jul 10, 2017 3:33 AM by Mangal Kamble RSS

    How to sum and divide variables in set analysis bar chart?

    ila ds

      Hello,

      i am new in qlik sense. I need to do a bar chart with suppliers as dimension and as measure i need the sum of the cancelled and closed items divided the received items.

       

      In loaded data i have an excel table with all the items with thei status (received, closed or cancelled) and the dates when they hae been received, closed or cancelled.

       

      In order to know the number of the closed, cancelled and received items in the analysis period I set three variables:

       

      eg.

      =Count({<[Data Cancellazione]= {">=$(=vStartOfPreviousMonth)<=$(=vEndOfPreviousMonth)"}>}[Numero CR])

       

      Now i am trying to create a new measure that is the sume of two variables (cancelled and closed items)/received items

       

      ((N_Cancellate)+(N_Chiuse))/(N_Ricevute)

       

      When I insert this measure in the bar chart the value is the same for all the suppliers.

       

      How can I get the right value for each supplier?

       

      Thanks

      Ila

        • Re: How to sum and divide variables in set analysis bar chart?
          Thomas Le Gall

          Hi Ila,

           

          I have maybe an idea : do you know that there is a big difference in defining a variable with or without a first "=" sign ?

           

          When, in your variable you write for your "eMyVariable" variable: "=sum(Sales)", the result will be always the same in the chart but if you write "sum(Sales)", the value will be well aggregated by the dimension in the chart.

           

          The reason is the variable is simply a set of characters, and when you're calling a variable, these characters will replace it.

           

          When in a chart you call $(eMyVariable), in the first example, the characters will be the result of the non-aggregated calculation (so if the total sum is 10000, the called characters are "10000" and every dimensiion value will show a 10000 value bar

           

          Without the "=" sign as in the second example, the characters will be "sum(Sales)", so the calculation will be made in the chart, and then well-aggregated.

           

           

          Also, please note a qlik sense issue: when you redefine a variable that contained a first "=" sign, it disappears and you have to write it again before saving the variable.

           

          BR,

          Thomas Le Gall

            • Re: How to sum and divide variables in set analysis bar chart?
              ila ds

              Hello Thomas,

              thanks for your suggestion. The issue was caused the "=" sign before the expression in the definiton of the variable. Now I corrected it in this way and it is working:

              Count({<[Data Ricezione]= {">=$(=vStartOfPreviousMonth)<=$(=vEndOfPreviousMonth)"}>}[Numero CR])

               

              and I also re-wrote the other expression in this way:

              (($(canc_1))+($(chiuse_1)))/($(ricevute_1))

               

              Now, I have  another question regarding an expression with multiple conditions. I have to calculate a number of items that are on time. The calculation should be count the number of CR that have the "Data avvio UAT pianificata" between the beginning of the month and the end of the month AND the Data avvio UAT effettiva less or egual than the Data avvio UAT pianificata AND different from null.

              I wrote the espression below but it returns a wrong value. It seems it is not taking into account the 2° and 3° conditions.

               

              count({<[Data avvio UAT pianificata]= {">=$(=vStartOfPreviousMonth)<=$(=vEndOfPreviousMonth)"},

              [Data avvio UAT effettiva]= {"<=$([Data avvio UAT pianificata])"},

              [Data avvio UAT effettiva]= {*}>}[Numero CR])

               

              Do you have any idea why?

               

              Thanks a lot,

              Ila

            • Re: How to sum and divide variables in set analysis bar chart?
              Mangal Kamble

              if you add '=' then it will evaluate answer and same will be applied everywhere instead if you want to store expression in variable don't use = in front of expression so that it will evaluate individually for everyone