Compare sales between a specific time range from different years

Q&A
1

Hello everyone,

I would like to request an help comparing the sum of Sales of a Customer with a specific interval this year and previous year.

I mean for example I have customer A that generate Sales for 23K in the time range 1/8 January 2023, I want to compare in a table with near columns the sum of sales, for the same customer and for the same specific time range from 2022.

I expect to see something like this:

First Column: Customer NAME

Second Column: sum of Sales from 1 to 8 January 2023 (eg: 23K) - This I can do easily

Third Column: sum of Sales from 1 to 8 January 2022 (eg: 15K)

In the Line I have a field indicating only the month number (01,02 etc…)

Screenshot 2023-01-10 alle 13.10.04

In my Screenshot the value on column JAN 2023 it’s the sum from 1 to 8 January 2023, but the value on column JAN 2022 it’s the sum of the entire JAN 2022 Month, I Aim to see the sum from 1 to 8 January 2022. Thank you for your help.

2

Hi,

do you have a line for every sale with a date in your data?

If yes, you could use this calculation:

ifelse({sales_month}>{current_month},0,
ifelse({sales_month}<{current_month},sum_sales,
ifelse({sales_day}<=({current_day}),sum_sales,0)
))

current_month = EXTRACT(‘MM’,now())
current_day = EXTRACT(‘DD’,now())
sales_month = EXTRACT(‘MM’,sales_date)
sales_day = EXTRACT(‘DD’,sales_date)

This way you calculate the sales up to the current day and you compare always the same time range.

If you want to change the enddate, you can create a parameter and set the default to Relative date and choose Start of This Day

image

Then you can use this parameter in the calculated fields, instead of now() and add a control for this parameter to change the date.