Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
tmumaw
Specialist II
Specialist II

Difference between dates in Days

Been searching the forum trying different things howerver nothing seems to work.  I have 2 dates created on (YYYY-MM-DD) and Resolve On (YYYY-MM-DD).  I need to find out how many days between these dates, but only do it were status is equal to '5'.  Thought I should be able to do this with set analysis, but having no luck.

Thanks
Thom

5 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Whether or not you can dod it with Set Analysis, depends on your chart dimensions. Since Set Analysis condition is evaluated only once per chart, you can't use it if your dimension is linked somehow to the two dates that you need to compare to.

My recommendation would be to calculate this information in the script, if possible, to move the heavy load away from the run-time. If your data is small, on the other hand, then you can use an IF statement instead of set Analysis, but keep in mind that this solution won't be scalable and won't perform on larger data sets.

cheers,

Oleg

johnw
Champion III
Champion III

Script vs. if() depends on whether you want the numbers to stay the same or change as you make selections.  Like if the dates are two years apart, but other selections can make it so that you're only interested in one month of those two years, do you want to count days in the month or days in the two years?  If the month, use if().  If two years, do it in script.

I think the if() would look something like this, assuming you have a calendar with a Date field.

count({<Status={'5'}>} distinct if(Date>=CreatedOn and Date<=ResolvedOn,Date)))

A more complicated option, if you want the results of the count(if()) instead of a fixed script solution, but need it to perform, is probably to handle it in the data model.  I suppose that's still a script solution but different than actually counting in the script.  Something like this, maybe, though it's hard to guess without knowing more about your data model.

Data:
ID, CreatedOn, ResolvedOn
A, January 4, January 7
B, January 5, January 6

NewTable:
ID, Date
A, January 4
A, January 5
A, January 6
A, January 7
B, January 5
B, January 6

count({<Status={'5'}>} distinct Date)

RicardoRamos
Employee
Employee

I'd suggest the use of the function networkdays(), i'm assuming that you want to get the weekends out...

tmumaw
Specialist II
Specialist II
Author

I finally got part of it to work.  I still have one piece that is not.

if(StatusCode <> '5', networkdays($(vToday),[Created On]),networkdays([Actual End Date],[Created On]))

The second part of the if works fine, it's the first part.  I have a variable created for Today in a YYYY-MM-DD format, but when I compare today to created on it returns the following:

Created On : 05/02/2011    Today : 05/10/2011    Duration : 27621

Any ideas?

Thanks
Thom

RicardoRamos
Employee
Employee

I'd try it with Makedate() to generate the Today function.

--

Ricardo

No dia 10/05/2011, às 15:40, "tmumaw" <qliktech@sgaur.hosted.jivesoftware.com<mailto:qliktech@sgaur.hosted.jivesoftware.com>> escreveu:

QlikCommunity<http://community.qlik.com/index.jspa>

Re: Difference between dates in Days

created by tmumaw<http://community.qlik.com/people/tmumaw> in Development (QlikView Desktop) - View the full discussion<http://community.qlik.com/message/114987#114987