9 Replies Latest reply on Jul 19, 2018 3:36 AM by Mahfooj Khan

# Calculating differences between the selected date and the max 1 day before

Hi all

May I know how to do it if I want to calculate the the differences between the selected date and the max 1 day before?

The reason i asked for max 1 day before is that let say above 1 day of selected date might be public holiday which is no data...

For eg in my workbook, the  selected date is 1/4/18, 1/3/18 got no data so the differences should be (1/4/18- 1/2/18) / 1/2/18

May I know how to achieve this?

• ###### 1. Re: Calculating differences between the selected date and the max 1 day before

Hi Haan,

First Create a calculation like below to get the -1 day value

after that create a calculation like below like to check if -1 date is 0 then take an -2 day

example for PJ measure the value for 1/04/2018 is 6500

there is no data for 1/03/2018

but for 1/02/2018 for PJ value is

DIfference would be 4500 for PJ

Hope this helps kindly mark this answer as complete or helpful so  that it will help others

1 of 1 people found this helpful
• ###### 2. Re: Calculating differences between the selected date and the max 1 day before

Dear Naveen

Thanks a lot!

However, if I have -3 or more?

Is there a way to take the nearest date to the selected one?

• ###### 3. Re: Calculating differences between the selected date and the max 1 day before

Is there any we can identify the day as holiday based on that I can write dynamic Calculation to take the max date

• ###### 4. Re: Calculating differences between the selected date and the max 1 day before

Dear Naveen

Basically there will be no data on the public holiday.

But I would like to have them to appear as zero for those that do not have data even though it is not a public holiday.

Thanks

• ###### 5. Re: Calculating differences between the selected date and the max 1 day before

If sales is not happened that means its holiday let me use this concept to identify it dynamically previous day sales

• ###### 6. Re: Calculating differences between the selected date and the max 1 day before

Dear Naveen

Thanks for ur help really.

But what if that day the sales is zero it will also appear as blank....

How ya....

• ###### 7. Re: Calculating differences between the selected date and the max 1 day before

Hi Haan,

Is there any dimension you can create storing all the public holiday and weekends so that joining that dimension with transaction table will help extract the holiday so that it will lot of help to identify max date.

Usually my approach create a calendar table with all the dates in a year and we create column next to the date with holiday_flag if holiday_flag = y means its means holiday based on that will be able to get the max(date)

Another thing is your data doesn't have all the dates information my suggestion would be to have date dimension it will be great life saver for this approach kindly let me know if any issues

• ###### 8. Re: Calculating differences between the selected date and the max 1 day before

Dear Naveen

There is another txt file that stated all the working days...is it ok?

I  have added in another data source.

• ###### 9. Re: Calculating differences between the selected date and the max 1 day before

Hi,