topic Re: Elapsed time calculations for data within selected date range in QlikView

Elapsed time calculations for data within selected date range

Wed, 03 Jul 2013 18:16:00 GMT

I have data setup as follows:

Start Date | End Date

A | B

I want the user to be able to select a date range from a drop down on a sheet, and I need to calculate if A and B (dates from the data) are within the selected date range. If they are, I need to calculate B-A (time elapsed), if not, I need to calculate only the time that elapsed in the selected date range.

Example:

Dates in data: 1/10/2013 to 1/15/2013

User-selected date range: 1/1/2013 to 1/13/2013

I want to calculate the elapsed time as 1/10/2013 to 1/13/2013 (3 days)

What is the best way to do this? I'm new with qlikview, but have php programming experience. Usually I would set the user-selected date range as a variable, and then write a function to calculate the elapsed time based on the data. How is this done in qlikview?

Any help is greatly appreciated.

Best,

Daniel

Re: Elapsed time calculations for data within selected date range

swuehl — Wed, 03 Jul 2013 18:24:05 GMT

Yes, you can create two input boxes for your two variables vEndDate and vStartDate, then try something like

=interval( Rangemin( vEndDate, B) - Rangemax(vStartDate, A) )

Take care that the dates entered into the variables match your standard date format set in the script.

Fields A and B needs to have a numeric representation, too, if not, parse your fields using date#() function.

Re: Elapsed time calculations for data within selected date range

Wed, 03 Jul 2013 18:52:33 GMT

Swuehl,

Thank you for the fast reply. Do I then put this =interval(....) in the expression on a chart? How do I display the calculated values for elapsed time?

Thanks

Daniel

Re: Elapsed time calculations for data within selected date range

swuehl — Wed, 03 Jul 2013 18:56:53 GMT

Probably yes, you haven't told much about your data set (e.g. the table that holds A,B) yet.

For example, in a chart with Dimension WorkOrderID (a field I assume to be in the same table like A,B for now), just use above as expression.

Or use something like

=interval( sum(Rangemin( vEndDate, B) - Rangemax(vStartDate, A) ) )

e.g. in a textbox to sum up multiple record's time span.

Re: Elapsed time calculations for data within selected date range

Wed, 03 Jul 2013 20:56:36 GMT

Ok. I put the interval(...) expression to display in a text box.

vStartDate = 6/4/2013

vEndDate = 6/7/2013

Text Box reads: 3

How do I know which row in the dataset is being calculated and displayed in the text box? What if I want to sum all the rows as follows:

Start Date | End Date | Calculated Value

A | B | 2days

C | D | 3days

Text box should read: 5 days.

Re: Elapsed time calculations for data within selected date range

swuehl — Wed, 03 Jul 2013 21:47:29 GMT

The text box will use all possible records (within your selection) for aggregation. It seems like you want to group your data, so you need to a chart and a field to group by as your dimension.

Hm, why don't you post some sample data, best using a small QV sample app, so we can help you with the charts and syntax?