To GoodData homepage
Solved

Buy sales this month with the same month last year

  • 23 June 2021
  • 2 replies
  • 131 views
jeferson.nunes
Userlevel 1
Good Morning! I need to create a metric to compare sales for this month (until today) with sales for the same month last year, but in equal periods.
I made a metric that compares the months, but I don't buy in equal periods being:

SELECT IFNULL(SUM(SALES),0) FOR PREVIOUS (Month/Year (Data),12)

❌ So he compares the following dates:06/01/2021 until 06/23/2021 (today) x 06/01/2020 until 06/30/2020.
✔️ I need him to compare the same dates being:06/01/2021 until 06/23/2021 (today) x 06/01/2020 until 06/23/2020 (today).

 

Thank you so much for your help!

icon

Best answer by Michael Ullock 23 June 2021, 17:51

View original

2 replies

jeferson.nunes
Userlevel 1

Hi Jeferson, 

thanks for reaching out. 

To achieve this, your metric would look like this:

For this Month/Year to todays date:

SELECT <SUM-Metric> WHERE Month/Year (Data) = THIS AND Day of Month (Data) BETWEEN D1 AND THIS

For the previous Month/Year to the same day of the month for the previous year: 

SELECT <SUM-Metric> WHERE Month/Year (Data) = THIS-12 AND Day of Month (Data) BETWEEN D1 AND THIS

The trick here is to use the "Day of Month" Attribute in your metric and then also select the "D1" value. In the above example, I have also added "<SUM-Metric>", there you would just insert your SUM metric. After this you should be all set. 

Best regards, 
M. Ullock 

 

Hi Michael, 

I verified the solution informed by you, but I was not successful because the data return always gave "No data"
I adapted your explanation and it worked this way:

SELECT IFNULL(SUM(Metric),0) FOR PREVIOUS (Month/Year (Data),12) WHERE Month/Year (Data) = THIS AND Day of Month (Data) BETWEEN D1 AND THIS

 

Anyway, your explanation solved my problem!Thank you very much.
Michael Ullock
Rank
Userlevel 2

Hi Jeferson, 

thanks for reaching out. 

To achieve this, your metric would look like this:

For this Month/Year to todays date:

SELECT <SUM-Metric> WHERE Month/Year (Data) = THIS AND Day of Month (Data) BETWEEN D1 AND THIS

For the previous Month/Year to the same day of the month for the previous year: 

SELECT <SUM-Metric> WHERE Month/Year (Data) = THIS-12 AND Day of Month (Data) BETWEEN D1 AND THIS

The trick here is to use the "Day of Month" Attribute in your metric and then also select the "D1" value. In the above example, I have also added "<SUM-Metric>", there you would just insert your SUM metric. After this you should be all set. 

Best regards, 
M. Ullock 

M. Ullock

Reply


Join us on
Welcome to the GoodData Community!Cookie settings