Date Date in Continuous Getting Number in Qlikview

The timeline above isnot to scale. The singularity is obviously much closer than we think.

What are we trying to accomplish?

We will tackle some scripting techniques to provide ways of grouping your dates into ranges and adding these buckets to your Master Calendar. I came across a neat use-case the other day concerning dates. The bulk of the data was focused on a date range of several years but there were some data that fell deep into the past as well as into the future. The goal was to maintain the selectability of the calendar we are used to that has a record for each day, while at the same time, retaining the dates outside this range and creating an easy way to group and select these dates.

To phrase another way, I want all the years that are important to be individually represented but then have the early years as one value and the future years as one value like this:

How to add date groups to your calendar table

I built this example in Qlik Sense but this could be used in QlikView just as well. I have attached the CalendarBucketScript.txt. In order for you to adapt this to your app, you will need to change the fact table names in lines 14, 105 & 123 to match your fact table and change the date field names in lines 13, 44, 92, 106, 110 and 124 to match the date field name in your fact table. Let's go thru the key items in the script

1. Where do you want the daily record calendar to start and stop?

We can use variables to hold the dates for the end of the "History Bucket" and for the beginning of the "Future Bucket". All dates in between will have a traditionalOne-Record per Day calendar. If you adjust the variables, they will work for the rest of the script. Notice you can have different logic for the history date and for the future date.

2. Create the range for your regular daily calendar

Next we will generate the normal calendar per usual. The only difference here is that when we create the range we will further reduce it by the variables we created. This means we will neglect the dates outside these parameters for now.

3. Create your normal calendar

I am not going to post this portion of the script (it is in the attachment) because there is not much change here. Create your traditional calendar control fields as you normally would. Then drop the TempCalendar as you normally would. But do note that there are some autonumber fields that will help me sort MonthYear and WeekYear and Year.

4. Add the History Bucket

Now we will create rows for each date that occurs before the minimum date of your calendar. Note that because we are creating a row for each date that falls outside out boundary, we will have a good key that ties back to the transaction table. The idea here is to create descriptor fields that make sense, ignoring fields that should not be grouped and using some ID fields for sorting purposes. This is most important because you will want "<1970" to appear before "1970 for example and ">2020" to appear after "2020". I decided to provide values to the buckets for display, based on the limit date in the format of whatever the field should be and then use a less-than sign (<), but there might be ways to do this more elegantly (?)

5. Add the Future Bucket

Lastly, we can add the rows that will account for the future date bucket. Much like we did for the History segmentation, we will apply some logic so that are grouping makes sense for list boxes and charts and also sorts correctly. I decided to use an arbitrary number of 100,000 because it is unlikely I would have that many dates so these will always be the highest values in our charts.

Displaying our Date Groupings in Qlik Sense

Now we can use list boxes (filter panes), and create charts that will display the calendar portion we really care about yet also retain our grouped history and future. And here is the same chart trended over year.

Final Thoughts

Here is some further reading regarding managing your date controls. Although this is not ground-breaking stuff, I think this can make a significant improvement in the usability of our QlikView or Qlik Sense date-related visualizations. There is no need to have a calendar from the days of the caveman to the singularity, but we also do not need to necessarily cut that data out. I found that this is a great way to deal with the issue. And just so you know, the Singularity is Upon Us. I welcome your thoughts or other tips that you would have to manage this problem.

gentileconstainey.blogspot.com

Source: http://livingqlikview.com/add-useful-date-groups-qlik-calendar/

Share :