topic Sugsestion:Date Range (Period) in QlikView

Suggestion:Date Range (Period)

Wed, 17 Jun 2009 20:34:10 GMT

Hello every one,

I need suggeation od date range selection.

In my dashboard I have to give date range selection (period). They are

WeekToDate, MonthToDate, YearToDate, Rollin12Months, Rolling3Months, last year, LastMonth, Custom Date Range and many more. By selecting any one of above I should get related data and Display date range for the selected period. I know there are many functions I can use; I am not sure how to use custom Date Range, rolling 3 months, rolling12months for which we don't have any functions available.

For custom range selection, We have to use Calander (Slidbar), so by selecting any period calander should change display of date based on selected period. So it will display the range.

If any one has suggestions and examples to do it in easy and better way, please feel free to post.

Thank you in advance.

Sugsestion:Date Range (Period)

Thu, 18 Jun 2009 00:38:58 GMT

Please let me know any one has solution or better and easy way to do it.

Sugsestion:Date Range (Period)

johnw — Thu, 18 Jun 2009 03:56:13 GMT

I've seen this before. Probably load a table with a Period field with values like 'Rolling 12 Months' and so on. Store the start and end of the periods on the table, then intervalmatch to a calendar. Seems like it would work.

Ah, the example I'm thinking of is in Rob Wunderlich's QlikView cookbook. You can download it from robwunderlich.com.

Sugsestion:Date Range (Period)

rwunderlich — Thu, 18 Jun 2009 07:43:02 GMT


John Witherspoon wrote:Ah, the example I'm thinking of is in Rob Wunderlich's QlikView cookbook. You can download it from robwunderlich.com.

The referenced example is named "Assigning dates to temporal groups such as "Yesterday"."

-Rob

Sugsestion:Date Range (Period)

Thu, 18 Jun 2009 20:12:51 GMT

Thank you all, Thank you for your reply

I will look into it.

Once agian thank you.

Sugsestion:Date Range (Period)

Thu, 18 Jun 2009 20:38:13 GMT

That is very good example, I have sort of same logic, the only issue i have is custom date range gice selection from calnder and so the start date and enddate of the period and not ever and every date in period.

Thank you for you help, if you have any suggestion please let me know.

Sugsestion:Date Range (Period)

Tue, 23 Jun 2009 03:27:16 GMT

Thank you for your help, but the Custom date is an issue, how to handle both together.

Please send me the pointer.

Once again thank you in advance.

Sugsestion:Date Range (Period)

johnw — Tue, 23 Jun 2009 03:40:35 GMT

There are lots of ways to handle a custom date range. If you MUST put 'Custom Date Range' in your list box, go ahead, but it obviously can't map to anything specific. So I would intervalmatch that value to ALL dates, so selecting it is the same as selecting nothing. But if I could get away with it, I'd wouldn't include that value, and I'd just have my users select nothing in that list box.

Regardless of what you do with the main list box, you'll need to handle the custom date range in some other way. There are lots of options. I typically just provide a hierarchy of Calendar fields - Year, Quarter and Year, Month and Year, Date. That works well if the date ranges of interest tend to be those sorts of periods. If the date ranges of interest tend to be fairly random, you might do better with a multi-value slider on the date field. And as a choice of last resort, you could have the user enter two dates, and then trigger a macro to select all dates between the two dates. I'd try to avoid that solution, though.

Sugsestion:Date Range (Period)

Tue, 23 Jun 2009 03:50:52 GMT

Thank you John,

Periods are great, that works well but the user requirement is custom date range from date to date. That is also needed. So not sure how to incorporate both together.

I am not sure how to do this? any example if have would be great.

Once again thank you for your time and help.


John Witherspoon wrote:
 If the date ranges of interest tend to be fairly random, you might do better with a multi-value slider on the date field.
<div></div>

Sugsestion:Date Range (Period)

rwunderlich — Tue, 23 Jun 2009 04:15:41 GMT

In the Cookbook example, selections in the "Range" field link to selections in the "Date" field. If you use a slider against the Date field, won't that make the selection you want?

Can you post an example qvw with your question?

-Rob

Sugsestion:Date Range (Period)

johnw — Tue, 23 Jun 2009 04:28:26 GMT

Here's the cookbook example modified to include a slider and 'Custom' as a range value, if that's what you're asking for.

Sugsestion:Date Range (Period)

rwunderlich — Tue, 23 Jun 2009 04:52:44 GMT

Nice modification John. I noticed some inconsistent display with the Slider in combination with a Range selection. If a range selection is made that falls outside the slider selection, the slider indicator clears. If the Range selection overlaps the slider selection, the slider inidicator remains unchanged -- although the Date selections are updated to reflect the selected Range.

Seems like an easy way to "fix" (hide?) this is to add a conditional show to the slider:

only(Range)='Custom'

-Rob

Sugsestion:Date Range (Period)

johnw — Tue, 23 Jun 2009 05:04:53 GMT

Any reason we need only()? Seems like just Range='Custom' would work. Want to make sure I'm not missing something. It might also be a good idea to set that there is always one selected value on the Range.

Sugsestion:Date Range (Period)

Tue, 23 Jun 2009 20:44:38 GMT

Thank you all for your feedback.

Please see the attached example. I am getting correct information for text box as well as calander when i select any period other then custom date range. Now when i select custome date range , I shold be able to select the custom date in the start date and end date calnder and the date should get selected accordingly. I am not able to figure it out.

Also Is it prssible to do interval match on multiple date fields for the same period text box. Right now i have interavlmatch on one date that is (date) field, If i want to do it on multiple date fields then is it possible?

I hope i have explained it properly.

Once again thank you for your help. Please let me know if i am missing any thing

Sugsestion:Date Range (Period)

johnw — Wed, 24 Jun 2009 02:23:54 GMT

All I can do is repeat myself, and refer you again to the example I posted. The Custom Date Range needs to map to the ENTIRE period, not just one month of the period. You then need a DIFFERENT way to select your dates, such as a multi-value slider on Date. I gave two other options in the earlier post as well.

You cannot select a start date and end date the way you have it set up because only ONE start date and ONE end date are mapped to 'Custom Date Range'. If you want to use two dates like this to select a range, they will need to be variables, and you will need to select everything in the range using a macro that you kick off whenever either variable changes. That wouldn't be my preference, but you could do it that way.

Sugsestion:Date Range (Period)

Thu, 25 Jun 2009 01:22:46 GMT

Any tips please?

Thank you

Sugsestion:Date Range (Period)

Tue, 16 Feb 2010 10:28:46 GMT

Dear John,

Thanks for the diamond code. This is very useful. But I need one more interval for last 4 week. These interval must include whole last 4 week . For example today is Tuesday, so the interval must indicate until the end of last Sunday. How can I add this to your sample code.

Hope to hear you soon.

Best Regards,

Sugsestion:Date Range (Period)

johnw — Tue, 16 Feb 2010 19:46:38 GMT

Probably this:

Previous 4 Weeks; weekstart(today(1),-4); weekend(today(1),-1)

Sugsestion:Date Range (Period)

Wed, 17 Feb 2010 07:21:06 GMT

Thank you ver much. It works properly.

Sugsestion:Date Range (Period)

Wed, 17 Feb 2010 07:23:03 GMT

Thank you very much John. It works properly.