Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Partner
Partner

KPI sum problem

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 IDReceipt IDItem IDSales Gross AmountSales Net AmountDiscountAction ID
1A11113.908.90--
1A20110.908.20--
1A20110.908.20--
1A1518.006.00--
1A111--5.00XY
1A201--2.70XY
1A201--2.70XY
1A151--2.00XY

The resulting chart has to be like this:

Action IDTotal SalesTotal Discount
XY31.3012.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

Tags (1)
12 Replies
Highlighted
Esteemed Contributor

Re: KPI sum problem

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])

Highlighted
Partner
Partner

Re: KPI sum problem

Hi Youssef,

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

Any idea ?

Highlighted
MVP
MVP

Re: KPI sum problem

Try putting NODISTINCT in aggr() like:

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

Highlighted
Esteemed Contributor

Re: KPI sum problem

Did you change the values of the table above ?

Highlighted
Partner
Partner

Re: KPI sum problem

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

Highlighted
Partner
Partner

Re: KPI sum problem

Hi Teemu,

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

Highlighted
Partner
Partner

Re: KPI sum problem

Am I missing sthing?

Capture.PNG

Highlighted
Contributor II

Re: KPI sum problem

Create a chart With

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

Expression 1.  Sum([Sales Net Amount])

                  2.    Sum(Discount)

1213.PNG

Highlighted
Partner
Partner

Re: KPI sum problem

Hi,

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

-Teemu