Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
edemerdjieva
Partner - Creator
Partner - Creator

Aggr() function problem

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:

Capture.JPG

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:

StoreFact SalesFact ReturnItem CodeQuantityReturn ReasonDateReceipt
S11A01-1Item default01/08/2017101
S3

1A01-1Change of mind01/08/2017102
S11A01415/09/2017203
S21A01122/09/2017311
S31A01104/10/2017405

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

Thanks in advance for your help.

Elina

14 Replies
shiveshsingh
Master
Master

What is your expected output from this table?

YoussefBelloum
Champion
Champion

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
Partner - Creator
Partner - Creator
Author

Hi Shivesh,

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

edemerdjieva
Partner - Creator
Partner - Creator
Author

Hi Youssef,

There is no change.

shiveshsingh
Master
Master

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

edemerdjieva
Partner - Creator
Partner - Creator
Author

Oups, sorry. It should be 4.

Capture.JPG

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
Partner - Creator
Partner - Creator
Author

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?