Sum of Rows:Total

[#1]

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!

[miguelbraga]

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

[#3]

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.

[miguelbraga]

You can make it something like this:

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

This might solve you're issue

Regards,

MB

[#5]

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!

[miguelbraga]

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

[#7]

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.

[miguelbraga]

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