Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

Calculate date but only within certain values


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...



Tags (3)
1 Reply
Valued Contributor II

Calculate date but only within certain values


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.



Community Browser