topic Re: YTD with aggregation in App Development

YTD with aggregation

Carlijn — Mon, 05 Feb 2024 15:32:27 GMT

Hi all,

I'm trying to create a YTD average amount spend per receipt, where I divide a sum of amount by the number of receipts. Condition in de denominator is to only take into account the receipts with total amount >0, a receipt can have multiple lines with products.

Here's my simplified denominator to calculate number of receipts with amount >0:

sum(if(aggr(Sum({$<Year, Month, Week, Day, Date = {">=$(=Date(yearstart(max(Date)),'DD-MM-YYYY'))<=$(=max(Date)),'DD-MM-YYYY'))"}>}[Amount]) , ReceiptId)>0,1,0))

When I filter a specific date, say 27-01-2024, then my filters become >=01-01-2024<=27-01-2024, which is ok. Problem however, is that the sum of the aggregation is equal to the number of ReceiptIds with amount >0 on exactly 27-01-2024. When I turn the if statement into a dimension in a table with date and receiptid, it works perfectly, it puts a 1 when amount > 0 and 0 when it's <= 0 for all dates between 01-01 and 27-01. However, when I turn it into a measure with sum(), suddenly only 27-01-2024 is taken into account.

Currently my average spend per receipt is enormous as it calculates the total amount for 01-01-2024 until 27-01-2024 divided by only the number of receipts of 27-01-2024.

Can anyone help me with what I'm doing wrong?

Thanks in advance!

Re: YTD with aggregation

Anil_Babu_Samineni — Tue, 06 Feb 2024 05:40:18 GMT

@Carlijn What is the formulae used for denominator?

Re: YTD with aggregation

Carlijn — Tue, 06 Feb 2024 11:13:31 GMT

@Anil_Babu_Samineni The formula I mentioned is for the denominator. For the numerator it is:

Sum({$<Year, Month, Week, Day, Date = {">=$(=Date(yearstart(max(Date)),'DD-MM-YYYY'))<=$(=max(Date)),'DD-MM-YYYY'))"}>}[Amount]).

So In total:

Sum({$<Year, Month, Week, Day, Date = {">=$(=Date(yearstart(max(Date)),'DD-MM-YYYY'))<=$(=max(Date)),'DD-MM-YYYY'))"}>}[Amount]).

sum(if(aggr(Sum({$<Year, Month, Week, Day, Date = {">=$(=Date(yearstart(max(Date)),'DD-MM-YYYY'))<=$(=max(Date)),'DD-MM-YYYY'))"}>}[Amount]) , ReceiptId)>0,1,0)).

Note: I use more fields in my set expressions (specific types and departments) but these would be in both my numerator and denominator so I excluded them here for simplicity

Re: YTD with aggregation

Anil_Babu_Samineni — Tue, 06 Feb 2024 13:04:48 GMT

@Carlijn I am trying to understand the data, You said the condition working as it should with dimension (With aggr()) and whereas you have moved the same to expression and it is not working?

Can we have some scramble data to demonstrate the same.