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

[edemerdjieva]

Here is the expected output:

Qty sold = sum(qty) per item and store

[sunny_talwar]

Try this for Quantity sold

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

[sasiparupudi1]

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

or

Sum(Aggr(Sum(nodistinct

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

[edemerdjieva]

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

[sunny_talwar]

Used to exclude rows where Return_Reason is null