KPI sum not equal to sum (Forced) in table

[madmax88]

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?

[lorenzoconforti]

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]

CONT_ID	Growth	Expression Part 1	Expression Part 2	Performance
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	-847	0	847

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 · Accepted solution]

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]

worked, thanks!

[madmax88]

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]

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]

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

Month	CONT_ID	Volume	Product	Performance
jan	1	100	share	1%
Feb	1	110	share	0.50%
Mar	1	120	share	0.05%
apr	1	100	share	-0.10%
may	1	150	share	1%
jan	2	50	share	1%
Feb	2	60	share	0.50%
Mar	2	50	share	0.05%
apr	2	60	share	-0.10%
may	2	80	share	1%
Net Growth	CONT_ID	Net Growth
jan	1
Feb	1	9.5
Mar	1	9.945
apr	1	-19.88
may	1	49
jan	2
Feb	2	9.75
Mar	2	-10.03
apr	2	10.05
may	2	19.4
Total		77.735