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

# Sum of Rows:Total

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?

• ###### Re: Sum of Rows:Total

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

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

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

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.

Any other ideas? Thanks in advance!

• ###### Re: Sum of Rows:Total

Hey there again,

Regards,

MB

• ###### Re: Sum of Rows:Total

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

Hey there again,

Do something like this in Load Script Editor:

YourTable:

sum(NegDOI) as TotalNegDOI;

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

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