Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
What is your expected output from this table?
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]))
Hi Shivesh,
The expected result is only one row with Qty Sold = 9 and Qty returned=-1
Hi Youssef,
There is no change.
Ok, can you share the app? How it's coming 9? i can see 6 from your shard data.
Oups, sorry. It should be 4.
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
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)
What is the expected output for the sample data that you have provided?