Aggr() function problem

[edemerdjieva]

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

[shiveshsingh]

What is your expected output from this table?

[YoussefBelloum]

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

[edemerdjieva]

Hi Shivesh,

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

[edemerdjieva]

Hi Youssef,

There is no change.

[shiveshsingh]

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

[edemerdjieva]

Oups, sorry. It should be 4.

[sunny_talwar]

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

[edemerdjieva]

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)

[sunny_talwar]

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