Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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.
Thanks to both
I tried to give a correct answer to a wrong approach
As always, small solutions to big problems
There is also, addyears(yourdate, -1)