How to get Sum of sales of a range dates this year and Sum of sales with the sames range of dates last year

[josecarmo]

On any day of the month I need to compare sales variation with the same range dates of last year.

How do I get sales for a range of dates for instance may 4th til 6th atual year to compare it with the same range days (may 4st til 6) of last year.

On this example supose we are on may 6th of 2021 and have sales from may 4th and need to compare how it goes with last year.

in black the dates selected this year 

in green the dates that should be compared last year 

in red the dates that should be excluded  when comparing last year 

LOAD * inline [

Date|Product|Sales

04/05/2021|001|20000

04/05/2021|002|30000

05/05/2021|001|35000

05/05/2021|002|30000

06/05/2021|001|32000

06/05/2021|002|26000

04/05/2020|001|70000

04/05/2020|002|70000

05/05/2020|001|65000

05/05/2020|002|40000

06/05/2020|001|52000

06/05/2020|002|56000

07/05/2020|001|52000

07/05/2020|002|26000

08/05/2020|001|22000

08/05/2020|002|26000

09/05/2020|001|22000

09/05/2020|002|16000

10/05/2020|001|12000

10/05/2020|002|16000

11/05/2020|001|12000

11/05/2020|002|16000

 ] (delimiter is '|');

Thanks a lot

[JuanGerardo]

Hi @josecarmo, you should decompose the date into their components (day, month, year) during the load, and then use some set analysis to get current vs prior year sales:

LOAD
	Day(Date)		AS Day,
    Month(Date)		AS Month,
    Year(Date)		AS Year,
    Product,
    Sales
inline [
Date|Product|Sales
04/05/2021|001|20000
04/05/2021|002|30000
05/05/2021|001|35000
05/05/2021|002|30000
06/05/2021|001|32000
06/05/2021|002|26000
04/05/2020|001|70000
04/05/2020|002|70000
05/05/2020|001|65000
05/05/2020|002|40000
06/05/2020|001|52000
06/05/2020|002|56000
07/05/2020|001|52000
07/05/2020|002|26000
08/05/2020|001|22000
08/05/2020|002|26000
09/05/2020|001|22000
09/05/2020|002|16000
10/05/2020|001|12000
10/05/2020|002|16000
11/05/2020|001|12000
11/05/2020|002|16000
 ] (delimiter is '|');
 

Then you can use following expressions with set analysis:

Current Year Sales: Sum({<Year = {$(=Max(Year))}>} Sales)

Previous Year Sales: Sum({<Year = {$(=Max(Year) - 1)}>} Sales)

JG

[josecarmo]

Hi Juan,

Thanks a lot for your suggestion! I'm already using set analysis for the year but what I'm lookinf for is a way to compare the corresponding range of days (ytd) of an incomplete month this year with the same days of prior year and not all the days of month last year.  

I need to compare the same range of days (without a manual selection of days) only the green circle (excluding the days in red from last year). is this possible?

Thanks 

[josecarmo · Accepted solution]

Hi to all,

I've find a way that works for me:

"LOAD * inline [ DateDoc|Product|Sales ..."

Sum( If(DateDoc <= DATE(ADDMONTHS(Today(), -12)) and Year(DateDoc) = Year(DATE(ADDMONTHS(Today(), -12))), Sales))

Hopefull it could be usefull to any one too.

Regards,

José