topic Re: Aggr() function problem in QlikView

Aggr() function problem

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

Hi all,

I need to calculate a KPI based on Aggr() function. Please find below the chart as well as the formula for the KPI in question:

Qty sold =

Sum(Aggr(Sum(TOTAL<[Return reason]>

{<[Fact Sales]={1},[Date]={">=$(v_PC_DD) <=$(v_PC_DF)"}>} [Qty]),[Country],[Business Unit],[Store],Product,[Item Code],[Return Reason]))

The underlying QV model is as follows:

Store	Fact Sales	Fact Return	Item Code	Quantity	Return Reason	Date	Receipt
S1		1	A01	-1	Item default	01/08/2017	101
S3		1	A01	-1	Change of mind	01/08/2017	102
S1	1		A01	4		15/09/2017	203
S2	1		A01	1		22/09/2017	311
S3	1		A01	1		04/10/2017	405

For some reason the formula doesn't work and I cannot figure out why.

Thanks in advance for your help.

Elina

Re: Aggr() function problem

shiveshsingh — Mon, 23 Apr 2018 10:25:20 GMT

What is your expected output from this table?

Re: Aggr() function problem

YoussefBelloum — Mon, 23 Apr 2018 10:27:02 GMT

Hi,

try this:

=Sum( {<[Fact Sales]={1},[Date]={">=$(v_PC_DD) <=$(v_PC_DF)"}>} Aggr(Sum(TOTAL<[Return reason]>

{<[Fact Sales]={1},[Date]={">=$(v_PC_DD) <=$(v_PC_DF)"}>} [Qty]),[Country],[Business Unit],[Store],Product,[Item Code],[Return Reason]))

Re: Aggr() function problem

edemerdjieva — Mon, 23 Apr 2018 10:28:07 GMT

Hi Shivesh,

The expected result is only one row with Qty Sold = 9 and Qty returned=-1

Re: Aggr() function problem

edemerdjieva — Mon, 23 Apr 2018 10:31:03 GMT

Hi Youssef,

There is no change.

Re: Aggr() function problem

shiveshsingh — Mon, 23 Apr 2018 10:39:02 GMT

Ok, can you share the app? How it's coming 9? i can see 6 from your shard data.

Re: Aggr() function problem

edemerdjieva — Mon, 23 Apr 2018 11:06:50 GMT

Oups, sorry. It should be 4.

Re: Aggr() function problem

sunny_talwar — Mon, 23 Apr 2018 11:09:48 GMT

Your expression contains field which you have not provided in your sample... I see few issues with the expression even without those fields, but it would be nice to see the whole picture...

One of the issue is that you are doing TOTAL <[Return Reason]>, but Return Reason is not even available for the rows where the quantity is not 0... how will you see them add up in that case? I think it might be easier to have a sample with the expected output from it to help you better here

Re: Aggr() function problem

edemerdjieva — Mon, 23 Apr 2018 13:19:00 GMT

Here is a sample application. I simplified the data. However, the data model differs a little bit from the original one (the fact table should only include return codes R1 and R2)

Re: Aggr() function problem

sunny_talwar — Mon, 23 Apr 2018 13:20:56 GMT

What is the expected output for the sample data that you have provided?

Re: Aggr() function problem

edemerdjieva — Mon, 23 Apr 2018 14:21:49 GMT

Here is the expected output:

Qty sold = sum(qty) per item and store

Re: Aggr() function problem

sunny_talwar — Mon, 23 Apr 2018 14:43:46 GMT

Try this for Quantity sold

Sum({<Return_Reason = {"*"}>}Aggr(NODISTINCT Sum({<Fact_Sales={1}>}Qty),Store,Item_Code))

Re: Aggr() function problem

sasiparupudi1 — Mon, 23 Apr 2018 18:11:38 GMT

May be try without return reason in the aggregation

Sum(Aggr(Sum(

{<[Fact Sales]={1},[Date]={">=$(v_PC_DD) <=$(v_PC_DF)"}>} [Qty]),[Country],[Business Unit],[Store],Product,[Item Code]))

Sum(Aggr(Sum(nodistinct

{<[Fact Sales]={1},[Date]={">=$(v_PC_DD) <=$(v_PC_DF)"}>} [Qty]),[Country],[Business Unit],[Store],Product,[Item Code],[Return Reason]))

Re: Aggr() function problem

edemerdjieva — Tue, 24 Apr 2018 07:50:58 GMT

Sunny, can you explain the impact of <Return_Reason = {"*"}>, please?

Re: Aggr() function problem

sunny_talwar — Tue, 24 Apr 2018 08:58:43 GMT

Used to exclude rows where Return_Reason is null