Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

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

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Analytics
- :
- Issue with Partial Sums

Options

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

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

Showing results for

Anonymous

Not applicable

2017-07-11
09:21 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Issue with Partial Sums

I have an invoicing report that comprehends the years of 2016 and 2017, and my goal is to check the average price change I had from 2016 to 2017.

I'm using the following Dimensions in a Pivot Table:

Division

Customer

Item

The first expression I used is to sum only how many items were sold that only appear in both years, so it is as follows:

**Qty 2016:**

if( sum ( {<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2016'}>} Qty ))

**Qty 2017:**

if( sum ( {<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2017'}>} Qty ))

Which worked perfectly, and I did the same to find how much was the average unit price that I sold for, which is the following:

**Avg. Price 2016:**

if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2016'}>} Total Invoiced ))

/ if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty )>0 ,sum( {<Year={'2016'}>} Qty))

**Avg. Price 2017:**

if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2017'}>} Total Invoiced ))

/ if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty )>0 ,sum( {<Year={'2017'}>} Qty))

And finally to compare both years I used three expressions, one to find the total invoiced on 2017, another to multiply the qty sold 2017 by the average price of 2016 and finally one to check the percetage that increased from one to the other:

**Total 2017: **

if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 ,sum( {<Year={'2017'}>} Total Invoiced ))

**Compare 2016:**

(if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>}Qty) >0 , sum( {<Year={'2016'}>} Total Invoiced ))

/ if( sum({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2016'}>} Qty )))

* (if( sum( {<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2017'}>} Qty )))

**% Price Change:**

((if (sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2017'}>} Total Invoiced )))

/ ((if( sum( {<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2016'}>} Total Invoiced ))

/ if( sum( {<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2016'}>} Qty )))

* (if( sum( {<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2017'}>} Qty ))))) -1

The final report is something like this:

The issue is when I go to the Chart Properties under the Presentations tab and select to Show Partial Sums to the DIVISION dimension, for example, it is not showing a straigth sum of the columns, but is actually showing a number that has nothing to do with anything. I only found that out after migrating the chart to Excel.

Any idea of how can I fix this? (Sorry for the long expressions, if you have a solutions to shorten it I'll appreciate a lot as well!)

1,578 Views

1 Solution

Accepted Solutions

juraj_misina

Luminary Alumni

2017-07-12
11:46 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hello Marcelo,

this one is a fun one. Check the attached file, I added a new sheet where I hope I used correct expressions for your case. I created two options for the last one (% Diff), one calculates overall % difference between the years (which makes sense to me), the other calculates % difference on individual rows and then sum of rows on total row (which was your original requirement I think).

1,341 Views

7 Replies

juraj_misina

Luminary Alumni

2017-07-11
11:58 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Marcelo,

there are two options:

Number one, you can switch to straight table, then go to table properties -> expressions, select the Price Change expression and change total mode to "sum Of Rows", then go to dimension limits tab and check "Show total" for your dimensions.

Number two is a bit trickier. If you need to have pivot table, then you need to change your expressions and use Aggr() function to calculate desired expression total (there's a difference between sum of rows and expression total, more info here: Totals in Charts). The resulting expression might look somewhat like this:

Sum(Aggr(If(sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0,

sum( {<Year={'2017'}>} Total Invoiced )/((sum( {<Year={'2016'}>} Total Invoiced )/sum( {<Year={'2016'}>} Qty )*sum( {<Year={'2017'}>} Qty )))

), DIVISION, CUSTOMER, ITEM))-1

By the way, since the condition is always the same, you can simplify most of your expressions using only one if statement.

Good luck!

1,341 Views

Anonymous

Not applicable

2017-07-11
01:53 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Juraj,

I used the following expression for the **Total 2017** and it worked perfectly:

Sum (Aggr (if (sum ({<Year={'2017'}>} QTY) >0 and sum( {<Year={'2016'}>} QTY) >0 ,

sum({<Year={'2017'}>} TOTAL_INVOICED )), DIVISION, CUSTOMER_GROUP, SUB_DIVISION, CUSTOMER_CODE, ITEM))

But when I try to compare with 2016 using 2017 quantities it's working only on each individual cell, but the total is still not a sum

(((Sum (Aggr (if (sum ( {<Year={'2017'}>} QTY) >0 and sum ( {<Year={'2016'}>} QTY) >0 ,

sum ( {<Year={'2016'}>} TOTAL_INVOICED)), DIVISION, CUSTOMER_GROUP, SUB_DIVISION, CUSTOMER_CODE, ITEM)))

/ (Sum (Aggr (if (sum ( {<Year={'2017'}>} QTY) >0 and sum( {<Year={'2016'}>} QTY) >0 ,

sum( {<Year={'2016'}>} QTY)), DIVISION, CUSTOMER_GROUP, SUB_DIVISION, CUSTOMER_CODE, ITEM))))

* (Sum (Aggr (if (sum( {<Year={'2017'}>} QTY) >0 and sum( {<Year={'2016'}>} QTY )>0 ,

sum( {<Year={'2017'}>} QTY)), DIVISION, CUSTOMER_GROUP, SUB_DIVISION, CUSTOMER_CODE, ITEM))))

And for the percentage change the same thing is happening, only each individual line is calculating correctly, but the bottom total line isn't.

(Sum(Aggr(if(sum({<Year={'2017'}>} QTY)>0 and sum({<Year={'2016'}>} QTY)>0 ,

sum({<Year={'2017'}>} TOTAL_INVOICED )), DIVISION, CUSTOMER_GROUP, SUB_DIVISION, CUSTOMER_CODE, ITEM)))

/

(((Sum (Aggr (if (sum ({<Year={'2017'}>} QTY) >0 and sum ({<Year={'2016'}>} QTY) >0 ,

sum ({<Year={'2016'}>} TOTAL_INVOICED)), DIVISION, CUSTOMER_GROUP, SUB_DIVISION, CUSTOMER_CODE, ITEM)))

/ (Sum (Aggr(if(sum({<Year={'2017'}>} QTY) >0 and sum({<Year={'2016'}>} QTY) >0 ,

sum({<Year={'2016'}>} QTY)), DIVISION, CUSTOMER_GROUP, SUB_DIVISION, CUSTOMER_CODE, ITEM))))

* (Sum (Aggr(if(sum({<Year={'2017'}>} QTY) >0 and sum({<Year={'2016'}>} QTY )>0 ,

sum({<Year={'2017'}>} QTY)), DIVISION, CUSTOMER_GROUP, SUB_DIVISION, CUSTOMER_CODE, ITEM))))

-1

Also, how do I simplify my expressions using only one if statement?

Thanks!

1,341 Views

juraj_misina

Luminary Alumni

2017-07-11
04:13 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hello Marcelo,

you certainly do not need to use Aggr() for Qty 2016, Qty 2017, Total 2017. Those three expressions were perfectly alright. Avg 2016 and Avg 2017 do not need Aggr neither, but can be simplified by using only one if statement:

- //2016
- if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2016'}>} Total Invoiced )/sum( {<Year={'2016'}>} Qty))
- //2017
- if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2017'}>} Total Invoiced )/sum( {<Year={'2017'}>} Qty))

Compare 2016 vs 2017 should probably be:

Sum(Aggr(If(sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0,

((sum( {<Year={'2016'}>} Total Invoiced )/sum( {<Year={'2016'}>} Qty )*sum( {<Year={'2017'}>} Qty )))

), DIVISION, CUSTOMER, ITEM))

And percentage change should work in a way I wrote before (at least I hope so 😞

- Sum(Aggr(If(sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0,
- sum( {<Year={'2017'}>} Total Invoiced )/((sum( {<Year={'2016'}>} Total Invoiced )/sum( {<Year={'2016'}>} Qty )*sum( {<Year={'2017'}>} Qty )))
- ), DIVISION, CUSTOMER, ITEM))-1

Let me know if this helped. If not, try uploading a sample application, maybe I can sort it out for you.

J

1,341 Views

Anonymous

Not applicable

2017-07-12
08:34 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hello Juraj!

For the Total 2017 I used the Aggr() function because without it the subtotal of the partial sum of the column was not working properly.

Follow an example, I added the expression for percentage change that I did, which is working on a row level, but not on the subtotal, and the one you sugested, that is working on a row level, but is also showing what is not found on both years, and I wanted to see only what was sold on both years (as the pictures below).

My expression

Your sugstion

1,341 Views

juraj_misina

Luminary Alumni

2017-07-12
11:46 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hello Marcelo,

this one is a fun one. Check the attached file, I added a new sheet where I hope I used correct expressions for your case. I created two options for the last one (% Diff), one calculates overall % difference between the years (which makes sense to me), the other calculates % difference on individual rows and then sum of rows on total row (which was your original requirement I think).

1,342 Views

Anonymous

Not applicable

2017-07-12
01:34 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Juraj,

It finally worked! Though I had to change the Compare 2016 field, on your file the expression was:

sum({<Year={'2016'}>} If(Aggr(NODISTINCT sum({<Year={'2017'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0

and Aggr(NODISTINCT sum({<Year={'2016'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0, [TOTAL INVOICED]))

/

sum({<Year={'2016'}>} If(Aggr(NODISTINCT sum({<Year={'2017'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0

and Aggr(NODISTINCT sum({<Year={'2016'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0, QTY))

*

sum({<Year={'2017'}>} If(Aggr(NODISTINCT sum({<Year={'2017'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0

and Aggr(NODISTINCT sum({<Year={'2016'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0, QTY))

What I did was add the Aggr() to embrace everything so it looked like this:

Sum(Aggr(sum({<Year={'2016'}>} If(Aggr(NODISTINCT sum({<Year={'2017'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0

and Aggr(NODISTINCT sum({<Year={'2016'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0, [TOTAL INVOICED]))

/

sum({<Year={'2016'}>} If(Aggr(NODISTINCT sum({<Year={'2017'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0

and Aggr(NODISTINCT sum({<Year={'2016'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0, QTY))

*

sum({<Year={'2017'}>} If(Aggr(NODISTINCT sum({<Year={'2017'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0

and Aggr(NODISTINCT sum({<Year={'2016'}>}QTY), DIVISION, [CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM)>0, QTY)),

DIVISION,[CUSTOMER GROUP], SUBDIVISION, [CUSTOMER CODE], ITEM))

Now it's working perfectly, thanks a lot for your help!

1,341 Views

juraj_misina

Luminary Alumni

2017-07-13
05:21 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hello Marcelo,

glad to hear it worked. One word of warning though, this is not the best approach performance-wise. If you plan to use this with large dataset or complex data model, try to create a flag for those items which meet the condition. Thus you would be able to use that flag in calculation or set analysis and dramatically simplify those expressions.

Best

Juraj

Community Browser