How to get the Same Day Last Year with Set Analysis

[joseluis_garcia]

I want to compare daily sales with the equivalent day last year. The"Calendar" table  contains both fields: DateOrder and SameDayLY.

I've tried countless expressions and the results are always wrong. The actual formula I use is

Amount_SDLY = Sum ({$ <SameDayLY=,DateOrder = {'$(=Date(Max(SameDayLY), "dd/MM/yyyy"))'}>} Amount)

Curiously, Date(Max(SameDayLY), "dd/MM/yyyy"))' is correct for every date.

I leave a sample document with dates in spanish format ('DD/MM/YYYY')

Thanks for your help

[Gysbert_Wassenaar · Accepted solution]

Set analysis expression are calculated once for the entire chart, not per row. So what you want is not possible with set analysis expressions.

What  you could do is add a line to your load script :

left join

LOAD DateOrder as SameDayLY, Amount as AmountLY Resident Sales;

This effectively adds a field AmountLY to your table than contains the Sales of the previous year. You can add the expression sum(AmountLY) to your chart.

[sbaldwin]

Hi, as you want to display it against the actual date you will struggle with just set analysis and you would need ifs etc which would be messy, try splitting the table into two with current and  previous tables then you can have two fields one for the current amount and one for previous, eg attached.

[joseluis_garcia]

Thanks to both

I tried to give a correct answer to a wrong approach

As always, small solutions to big problems

[christian77]

There is also, addyears(yourdate, -1)