Sign InHelp

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion board where members can get started with Qlik Sense.

- Qlik Community
- :
- Qlik Sense
- :
- Qlik Sense Enterprise
- :
- Qlik Sense Enterprise Discussions
- :
- New to Qlik Sense
- :
- Sum of Rows:Total

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

07-18-2016
10:04 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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!

967 Views

7 Replies

miguelbraga

Valued Contributor III

07-18-2016
11:33 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

10 Views

Not applicable

07-18-2016
12:16 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

10 Views

miguelbraga

Valued Contributor III

07-18-2016
12:32 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

You can make it something like this:

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

This might solve you're issue

Regards,

MB

10 Views

Not applicable

07-18-2016
01:41 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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!

10 Views

miguelbraga

Valued Contributor III

07-19-2016
04:29 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

10 Views

Not applicable

07-19-2016
10:00 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

10 Views

miguelbraga

Valued Contributor III

07-19-2016
12:35 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

10 Views