Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
madmax88
Creator II
Creator II

KPI sum not equal to sum (Forced) in table

Hi,

I have an expression (2 Parts):

(sum({$<Month={"$(=Date(max(Month)))"},Product={'share'}>}Volume)-
sum({$<Monat={"$(=Date(AddMonths(max(Month),-1)))"},Product={'share'}>}Volume))
-
(sum({$<Month={"$(=Date(AddMonths(max(Month),-1)))"},Product={'share'}>}Volume)*
avg({$<Month={"$(=Date(max(Month)))"},Product={'share'}>}Performance))

Target is a Volume Growth without performance. 

Both Part-expressions are working separaterly:

(sum({$<Month={"$(=Date(max(Month)))"},Product={'share'}>}Volume)-
sum({$<Monat={"$(=Date(AddMonths(max(Month),-1)))"},Product={'share'}>}Volume))

Is fine and also the performance*Volume

But together it give me false Total values. In a table i need to force the sum to have to correct Total value (The sum by share is correct). But in the KPI Chart I can't force the sum and then it's worng. Any idea how to solve it?

1 Solution

Accepted Solutions
lorenzoconforti
Specialist II
Specialist II

You need to wrap your formulas around an aggregation by your CONT_ID dimension so that total calculate correctly:

Expression Part 2:
sum(aggr((sum({$<Month={"$(=Date(AddMonths(max(Month),-1)))"},Product={'share'}>}Volume)*
avg({$<Month={"$(=Date(max(Month)))"},Product={'share'}>}Performance)),CONT_ID))

 

Growth (just in case, check if the field Monat is correct):

sum(aggr(

(sum({$<Month={"$(=Date(max(Month)))"},Product={'share'}>}Volume)-
sum({$<Monat={"$(=Date(AddMonths(max(Month),-1)))"},Product={'share'}>}Volume))
-
(sum({$<Month={"$(=Date(AddMonths(max(Month),-1)))"},Product={'share'}>}Volume)*
avg({$<Month={"$(=Date(max(Month)))"},Product={'share'}>}Performance))

,CONT_ID))

View solution in original post

7 Replies
lorenzoconforti
Specialist II
Specialist II

It's a bit hard to follow; I'd say you probably have an aggregation issue. Can you share a sample dashboard? It would make it easier to help you

madmax88
Creator II
Creator II
Author

CONT_IDGrowthExpression Part 1Expression Part 2Performance
1-1’545-2’501-956-0.53%
2-2’030-2’145-115-0.06%
3-6’282-10’089-3’807-0.52%
4-1’380-2’492-1’112-0.67%
5-1’720-2’817-1’097-0.54%
6-0--
7-5’100-5’389-289-0.06%
8-3’769-8’200-4’431-0.84%
9-2’553-4’144-1’591-0.53%
10-363-4’762-4’399-0.94%
Sum-25’588-42’539-16’951-0.52%
Forced Sum-24’741-42’539-17’798 
Difference-8470847 

 

It seems there is a problem by calculating the performance with the volume.

Expression Part 1:
(sum({$<Month={"$(=Date(max(Month)))"},Product={'share'}>}Volume)-
sum({$<Monat={"$(=Date(AddMonths(max(Month),-1)))"},Product={'share'}>}Volume))
-

Expression Part 2:
(sum({$<Month={"$(=Date(AddMonths(max(Month),-1)))"},Product={'share'}>}Volume)*
avg({$<Month={"$(=Date(max(Month)))"},Product={'share'}>}Performance))

Performance:
avg({$<Month={"$(=Date(max(Month)))"},Product={'share'}>}Performance)
The Performance value is a monthly value which is changeing every month

lorenzoconforti
Specialist II
Specialist II

You need to wrap your formulas around an aggregation by your CONT_ID dimension so that total calculate correctly:

Expression Part 2:
sum(aggr((sum({$<Month={"$(=Date(AddMonths(max(Month),-1)))"},Product={'share'}>}Volume)*
avg({$<Month={"$(=Date(max(Month)))"},Product={'share'}>}Performance)),CONT_ID))

 

Growth (just in case, check if the field Monat is correct):

sum(aggr(

(sum({$<Month={"$(=Date(max(Month)))"},Product={'share'}>}Volume)-
sum({$<Monat={"$(=Date(AddMonths(max(Month),-1)))"},Product={'share'}>}Volume))
-
(sum({$<Month={"$(=Date(AddMonths(max(Month),-1)))"},Product={'share'}>}Volume)*
avg({$<Month={"$(=Date(max(Month)))"},Product={'share'}>}Performance))

,CONT_ID))

madmax88
Creator II
Creator II
Author

worked, thanks!

madmax88
Creator II
Creator II
Author

And another question: This expression always compares the difference of the last 2 months and this difference is the growth value.

Is it also possible to show a YTD value for all months? Target is the cumulative sum calculated by month. Instead of writing the expression for each month it would be helpful to have on expression as whole year (YTD)

(sum({$<Month={"$(=Date(max(Month)))"},Product={'share'}>}Volume)-
sum({$<Month={"$(=Date(AddMonths(max(Month),-1)))"},Product={'share'}>}Volume))
-
(sum({$<Month={"$(=Date(AddMonths(max(Month),-1)))"},Product={'share'}>}Volume)*
avg({$<Month={"$(=Date(max(Month)))"},Product={'share'}>}Performance))

,CONT_ID))

lorenzoconforti
Specialist II
Specialist II

YTD is possible and there are various posts that deal with this; see this:

https://community.qlik.com/t5/QlikView-Documents/YTQ-QTD-MTD-and-WTD/ta-p/1489245

What so you mean by "cumulative sum calculated by month"? Can you share an example of what it would look like?

madmax88
Creator II
Creator II
Author

Yes, i saw various posts, but no one has a solution. Problem is following:

With the expression mentioned in the topic i calculate the growth value in a month by taking the difference between 2 months and removing the performance to have the net value. The problem for YTD value is that I only have the monthly performance and therefor I need to calculate the Net Value by month. It's not possible to take an average performance of all months so i have to calculate for each month the net value and then take the overall sum of all months..

Below an example: I need the Total

MonthCONT_IDVolumeProductPerformance
jan1100share1%
Feb1110share0.50%
Mar1120share0.05%
apr1100share-0.10%
may1150share1%
jan250share1%
Feb260share0.50%
Mar250share0.05%
apr260share-0.10%
may280share1%
     
     
     
Net GrowthCONT_IDNet Growth  
jan1   
Feb19.5  
Mar19.945  
apr1-19.88  
may149  
jan2   
Feb29.75  
Mar2-10.03  
apr210.05  
may219.4  
Total 77.735