Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

c_neumann
Not applicable

Subtracting dates

Hi

I need some help with calculating close days for cases

If the cases are still open (Case Completed Field) the Disciplinary
Interview Outcome Date
= 9999-12-31.

So for open days on open cases I use this formula:

=num(Today())-num([Date of Alleged Offence])

Close days on close cases I use this formula:

=num([Disciplinary Interview Outcome Date])-num([Date of Alleged Offence])

  • I would like to use one formula for open and closed cases to
    calculate Case Days. Could you assist with a possible solution or if
    statement?

  My problem is that it shows 2 917 832 days
for open cases on my close case days formula

Days.png

1 Solution

Accepted Solutions
PrashantSangle
Not applicable

Re: Subtracting dates

Hi,

NUM(Date#('9999-12-31','YYYY-MM-DD')) is equal to 2958465

therefore you can try like

if(num([Disciplinary Interview Outcome Date])=2958465,

num(Today())-num([Date of Alleged Offence]),

num([Disciplinary Interview Outcome Date])-num([Date of Alleged Offence])

)

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
1 Reply
PrashantSangle
Not applicable

Re: Subtracting dates

Hi,

NUM(Date#('9999-12-31','YYYY-MM-DD')) is equal to 2958465

therefore you can try like

if(num([Disciplinary Interview Outcome Date])=2958465,

num(Today())-num([Date of Alleged Offence]),

num([Disciplinary Interview Outcome Date])-num([Date of Alleged Offence])

)

Regards

Great dreamer's dreams never fulfilled, they are always transcended.