Calculate date but only within certain values

[boryspower]

Hi,

I load a callendar with working days (dates without weekends and holidays) and I have a variable with manually inputed date... than I would like to calculate other date=input_date minus few days, but I should not count dates that are not within working days... To be better understand I attach a file and picture with explanation

Maybe you will have some brilliant idea how to do it...

BR

Borys

[fosuzuki]

Hi

I've got 2 solutions for you (I'm not really an expert so I can't tell you if any of them is the best solution)

1. Use the formula: FieldValue('Date', FieldIndex('Date', $(date_on)) - X)

(One problem with this approach is that the records in the Date field must be loaded in an ascending sort so that the functions FieldValue and FieldIndex can correctly locate the dates)

2. Use the formula: SubField(concat({<Date={"<=$(date_on)"}>} Date, '|',Date), '|', count({<Date={"<=$(date_on)"}>} distinct Date)-X)

In both formulas, replace the X with the number of days you want to go back.

By the way, I think that what you want to achieve is that after the reload, when the user inserts the date_on, the values in the variables should be updated without another reload, right? because your current app would require a second reload to update the values in the variables...

If you need detailed explanation on each approach, let me know.

Regards,

Fernando