Simplifying time period analysis for Qlik self-service users

Disclaimer: you can search time periods using keywords in the Qlik Insight Advisor, so this would be a complement to that.

A common challenge for Qlik self-service users is writing expressions, and the most common reason for writing expressions is time period-based reporting. I have an idea that may simplify these time period expressions -- and filtering or grouping by time periods -- which I call the self-service time period table.

It's a table with two columns: a date and a time period.

1. A date will have multiple rows if it is part of multiple time periods. For example, "today" is part of the current week, current month, current year, and current quarter.
2. The time period contains plain English abbreviations of time periods, e.g., CYTD, PMTD -- these could be anything your users would understand.

 Pros

• Just one field is used for all periods, not a separate flag per time period.
• Period values are unique to the field we care about, which makes searching easier, unlike Y/N or 0/1 flag fields when you have a field for each time period.
• Enables very simple time period expression logic, both Set Analysis and conditional aggregation.
• Sum({<Period = {'CYTD'}>} Sales)
• Sum(If(Period = 'CYTD', Sales))
• Values are dynamic and shift over time, so bookmarking or creating expressions that use, e.g., Period = 'CMTD', will automatically move ahead and continue to show the new current month, as time passes.
  
• Values are accessible in Smart Search (the global search at the top), without taking users out of the flow of the sheet. It also works well as a filter on self-service sheets.
  
• The table scales up or down without changing its structure, no matter how many time periods you wish to capture.
• "Today" is easily determined and set in the script/data model and can dynamically reflect the freshness of the data, whereas NLP searching usually defaults to the system date without additional work.
• The calendar is built based on your business' calendar. NLP search often requires you to jump through hoops to accommodate fiscal periods, 4-4-5, or other non-standard calendars. If you can write the expression for it in the script, it will work here.
  

Cons

• Periods are "as of" one date only, at least as demonstrated below. This could be expanded, but would be more complex to use and may grow considerably in row count.
• Using this field in formulas may not perform as well as numeric flags, for large apps.
• Relies on a developer to create this table in the first place!

How to build a self-service time period table

1. Set v_Today to the 'as of date' from which to calculate your time periods. To reuse the code you create for this, I recommend using a variable for the date field name, too. That will port well to a subroutine.

2. Write a CrossTable load with your date key field and any number of expressions yielding 1/0 values if the date falls in the specified time period. Note that we are calling this is a temp table because the final table will be created in the next step.

 The CrossTable load results in a table with these columns:

1. the date field
2. Period: contains the names of the flag fields, e.g., CY, CYTD
3. FlagInPeriod: 0/1 values of the flag fields

3. Create final table, filtering to the '1' rows, and drop the temp table.

   
 

 

 

It's just an idea, so let me know what you think.

In the future I may cover how other platforms solve this. Power BI in particular has a unique implementation of time period logic using DAX.