7 Replies Latest reply: Jul 19, 2016 12:35 PM by Miguel Braga RSS

    Sum of Rows:Total

    Sarikonda Akhil

      Hi,

       

      I was able to calculate the sum of rows for a measure in a table by using the shortcut on the front end in Qlik Sense by going to the measure and choosing the "Total" as "sum". But, I want to now display this sum using the KPI module and possibly use this same number in other calculations. Is there any way this can be done?

       

      Thanks in advance!

        • Re: Sum of Rows:Total
          Miguel Braga

          Hey there,

           

          You can do this by adding a field in your script editor like this:

           

               sum(TOTAL Sales) as TotalSales,

           

          Then you add this field to a KPI object. Or if you like another approach, make a variable like this either in script:

           

               LET vTotalSales = sum(TOTAL Sales)

           

          Or the definition of variables in Sheet Editor. After that make a KPI with that variable.

           

          Hope this helps,

           

          Regards,

          MB

            • Re: Sum of Rows:Total
              Sarikonda Akhil

              That doesn't seem to work. I tried doing that but it either shows a null value or zero.

               

              I know that when I was trying to create it on the table it showed zero when I didn't sum it by rows. Is there any actual definition to sum by rows?

               

              I tried creating this as the measure: vTotalSales (measure name) = sum(TOTAL NegDOI)

               

              NegDOI holds the value of missed inventory. If I sum the column using Total->Sum on the table it shows the correct value.  But, not elsewhere.

                • Re: Sum of Rows:Total
                  Miguel Braga

                  You can make it something like this:

                   

                  vTotalNegDOI = sum({1<NegDOI = >} TOTAL NegDOI)

                   

                  This might solve you're issue

                   

                  Regards,

                  MB

                    • Re: Sum of Rows:Total
                      Sarikonda Akhil

                      Miguel thank you for your continued help. I don't think this worked though.

                       

                      Here is a screenshot of the column. Basically, when I do Totals function->Sum on the Qlik Sense editor it shows the sum as 112.225644873. I now need this value so that I can display it using the KPI Function. But, I also need to use the value in another calculation.

                       

                      I think the reason I'm having trouble using it elsewhere is that NegDOI is a calculated measure. So, it's defined using other variables to calculate.

                      Capture.PNG

                      Any other ideas? Thanks in advance!

                        • Re: Sum of Rows:Total
                          Miguel Braga

                          Hey there again,

                           

                          By only seeing this screenshot I think your Total calculation is wrong... Please, tell me what is your expression in your measure? That way I can help you better

                           

                          Regards,

                          MB

                            • Re: Sum of Rows:Total
                              Sarikonda Akhil

                              Hi Miguel,

                               

                              The actual total calculation is correct. There's another 50 or so rows, and I hand calculated it to make sure it's showing the right value, and it is.

                               

                              NegDOI is a calculated measure. I calculate it using the following:

                              if(DOI<=1,(Sum(BEGNG_INVTY_QTY)-Sum(WSF_FCST_QTY))*BARREL_FCT,0)

                               

                              Essentially, the column DOI is a calculated measure that determines if there is less than 1 days of inventory. If it is less than or equal to 1, we calculate how much inventory is missed. This is done by subtracting weekly forecast quantity from beginning inventory that week. I simply need the aggregated sum of that value across different dimensions such as COMPANY_NUMBER, PRODUCT_CODE.

                                • Re: Sum of Rows:Total
                                  Miguel Braga

                                  Hey there again,

                                   

                                  Do something like this in Load Script Editor:

                                   

                                  YourTable:

                                  LOAD *,

                                            sum(NegDOI) as TotalNegDOI;

                                  LOAD *,

                                            if(DOI<=1, Value, 0) as NegDOI;

                                  LOAD *,

                                            (Sum(BEGNG_INVTY_QTY)-Sum(WSF_FCST_QTY))*BARREL_FCT as Value

                                  FROM [your source database];

                                   

                                  With this two preceding loads you can get the Total value that you're looking for...

                                   

                                  Regards,

                                  MB