cancel
Showing results for
Did you mean:
Partner - Contributor II

## How to get the Same Day Last Year with Set Analysis

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')

1 Solution

Accepted Solutions

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

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.

talk is cheap, supply exceeds demand
4 Replies

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

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.

talk is cheap, supply exceeds demand
Partner - Creator III

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.

Partner - Contributor II
Author

Thanks to both

I tried to give a correct answer to a wrong approach

As always, small solutions to big problems

Partner - Specialist