topic Re: KPI sum problem in QlikView

KPI sum problem

edemerdjieva — Wed, 25 Nov 2020 16:16:04 GMT

Hello all,

I have a chart displaying marketing action performance based on two KPI : total of sales and total of discounts.

The Fact table has the following fields:

Store ID	Receipt ID	Item ID	Sales Gross Amount	Sales Net Amount	Discount	Action ID
1	A	111	13.90	8.90	-	-
1	A	201	10.90	8.20	-	-
1	A	201	10.90	8.20	-	-
1	A	151	8.00	6.00	-	-
1	A	111	-	-	5.00	XY
1	A	201	-	-	2.70	XY
1	A	201	-	-	2.70	XY
1	A	151	-	-	2.00	XY

The resulting chart has to be like this:

Action ID	Total Sales	Total Discount
XY	31.30	12.40

The problem is with the Total Sales Net Amount, defined as follows:

=Sum(Aggr(Sum(TOTAL <[Receipt ID]> [Sales Net Amount]),[Action Id], [Receipt ID]))

it takes into account distinct items. So in the example above the result is : 23.10 which is not correct. The good value should be 31.30.

Any idea how to write the expression?

Ce message a été modifié par : Elina Demerdjieva

Re: KPI sum problem

YoussefBelloum — Tue, 20 Feb 2018 16:55:46 GMT

Hi,

try with this:

=sum(aggr(sum([Sales Net Amount]),[Marketing Action ID],[Receipt ID]))

or with this:

=sum(total <[Receipt ID],[Marketing Action ID]> [Sales Net Amount])

Re: KPI sum problem

edemerdjieva — Wed, 21 Feb 2018 10:14:52 GMT

Hi Youssef,

None of these work. I added some details in the description to help the understanding of the underlying structure.

Any idea ?

Re: KPI sum problem

tresB — Wed, 21 Feb 2018 10:19:35 GMT

Try putting NODISTINCT in aggr() like:

=Sum(Aggr(NODISTINCT Sum(TOTAL <[Receipt ID]> [Sales Net Amount]),[Action Id], [Receipt ID]))

Re: KPI sum problem

YoussefBelloum — Wed, 21 Feb 2018 10:23:02 GMT

Did you change the values of the table above ?

Re: KPI sum problem

teempi — Wed, 21 Feb 2018 10:37:03 GMT

Hi,

Based on the data you provided, I would probably try to separate the discounts into a new table and link them to sales by store id, receipt id and item id (use autonumber or any other method to create composite key). This way each action id should link properly to the sales rows.

Then you should be able to get rid the aggr-expression and just use a normal sum. Seems much easier.

-Teemu

Re: KPI sum problem

edemerdjieva — Wed, 21 Feb 2018 10:45:10 GMT

Hi Teemu,

I cannot change the data model, unfortunately. I tried the NODISTINCT qualifier but there is no effect on the Total Sales.

Re: KPI sum problem

OmarBenSalem — Wed, 21 Feb 2018 11:01:48 GMT

Am I missing sthing?

Re: KPI sum problem

qlik4asif — Wed, 21 Feb 2018 11:07:45 GMT

Create a chart With

Calculated Diemsion as =PIck(WildMatch([Action ID],'XY','*-*'),'XY','XY')

Expression 1. Sum([Sales Net Amount])

2. Sum(Discount)

Re: KPI sum problem

teempi — Wed, 21 Feb 2018 11:29:13 GMT

Hi,

What if there are multiple different actions? I'm assuming it's possible.

-Teemu

Re: KPI sum problem

OmarBenSalem — Wed, 21 Feb 2018 11:32:57 GMT

In that case, we alter it as follow:

sum(total <ActionID> Measure)

Re: KPI sum problem

Anonymous — Wed, 21 Feb 2018 12:51:54 GMT

Hello Elina,

Can you try this below expression and let me know whether the expression is working as you expected or not.

Expression : =Sum({<[Receipt ID] = , [Action ID] = ,[Item ID] = >} [Sales Net Amount])

Thanks & Regards,

Venkata Sreekanth

Re: KPI sum problem

edemerdjieva — Fri, 23 Feb 2018 09:24:45 GMT

Hi,

I found the solution. I added a row number to my data. Then the expression worked fine as there was a unique identifier.

Sum(Aggr(Sum(TOTAL <[Receipt ID]> [Sales Net Amount]),[Action Id], [Receipt ID]))

Thanks to all for helping!