Qlik Community

QlikView Documents

Documents for QlikView related information.

Calendar with flags making set analysis so very simple

Luminary
Luminary

Calendar with flags making set analysis so very simple

NOTE: A more recent version with added functionality can be found here: Calendar with AsOf Flags, Compare Easter to Easter

Hello

Calendars are one of the first things developed when working with Qlik and most follow a similar format.

Often called “Master Calendar” the name misleads as best practice design means multiple calendars are often
required. http://community.qlik.com/docs/DOC-6502

Once you have a master calendar you’ll often find yourself creating complex set analysis to calculate date ranges or comparative calculations like Month to Date or Previous Month. You can find some good examples here:

http://community.qlik.com/docs/DOC-6163

Set Analysis for Rolling Periods

Set Analysis for certain Point in Time

This QVW describes an alternative way. The calendar here creates all possible combinations of dates and flags their relationship with each other.

Calendar1.png

Here we can see it in action. The Date Selected combined with the Flag returns the Possible Key Dates which are linked to the fact table. The Set Analysis becomes very intuitive and easy to maintain:

Yesterday:=sum({<Date={'$(=max(Date))'},%Flag_PreviousDay_M01={1}>}Sales)

Additional flags shown here you take the concept further.

MTD:=sum({<Date={'$(=max(Date))'},%Flag_ThisMonthToDate={1}>} Sales)
Previous MTD:=sum({<Date={'$(=max(Date))'},%Flag_PreviousMonthComparative_M01={1}>} Sales)
Previous Month:=sum({<Date={'$(=max(Date))'},%Flag_PreviousMonth_M01={1}>}Sales)
Same Month Last Year:=sum({<Date={'$(=max(Date))'},%Flag_PreviousMonth_M12={1} >}     Sales)


This idea was originally shown to me by a colleague Jason Michaelides who was inspired by A New Choice of Armor: The Flag Matrix | iQlik - Everything QlikView. This version takes the concept and by simplifying the code means its easier to understand and allows you to develop further to meet your individual requirements.

The basic development principles:

Firstly a standard Calendar is created using AutoGenerate. I also take the opportunity to incorporate Business Day flags (set by loading regional public holidays).

A Cartesian Product is then produced by left Joining Distinct Dates with itself without the key. This gives us all the possible date
combinations.

I then create the flags; Firstly Point In Time Flags such as Today, This Week, This Month, etc. Then period flags such as Previous Day, Previous Month, Previous Month to Date, etc.

Update - Following the comments I've added more code to the CLEAN UP tab to remove any lines where no flags have been set and any lines where the Possible Date is higher than the selected date which reduces the tables size by over 50%

Update - Fixed a bug resulting from the previous update

Update 22-Aug-2014 - Fixed a bug which reduces the number of rows in the final calendar

I hope this of some use to people, I would love to hear your comments and further suggestions.

Richard

qlikcentral | Understand / Create / Inform

Attachments
Comments
Partner
Partner

Hi, Richard. Great doc!

I believe you did non mention yet another reason for that approach: It not only make set expressions for point in time analysis much more manageable. It make possible some scenarios of analysis which are impossible with set analysis only. For example: How to trend Current Sales vs Prev Sales, using date as dimension?

0 Likes
Marcio_Campestrini
Valued Contributor

Hi Richard.

Thanks th post. I just started developing something in this way. It'll help me a lot.

Luminary
Luminary

This is what's generally known as an AsOf calendar table. I often use this when a client requires rolling-n month visualization. For general point in time reporting, I use the approach as described here by Miguel Garcia : The Magic of Set Analysis – Point In Time Reporting | iQlik - Everything QlikView. By assigning sequential numbers to each element of your calendar (day, week, month, quarter, year, etc.) point in time reporting suddenly becomes very simple.

0 Likes
Partner
Partner

I tried to use similar approach but as I understood it works only if your base date is today()

If you use the selected date as the base date to do calculation you need more complex set analysis like

Month={$(=Month(AddMonths(Max(Calendar.Date),-1)))} etc...

0 Likes
Partner
Partner

Hi Sergey.

Did you look at attached Calendar.qvw? Last sheet - "Example Set Analysys" demonstrate how it works with any selected period. Without complex set expressions. With expressions like

=sum({<Date={'$(=max(Date))'},%Flag_PreviousMonth_M01={1} >}Sales)

I believe that is whole point of this document.

Partner
Partner

Hi Vadim,

Yes, You are right. It works. I should get my words back.

I see only one problem if you need to use Month as dimension and show what is actual month for the value.

Because in your example I can see the selected month.

But anyway - thank you for sharing. It's very interesting.

I usually use variables and my expressions usually look like:

$(eAmount($(vYTD)))

or $(eAmount($(vPrevMonth)))

It's gives simplicity to read the code as well. But also gives me a lot of brackets

Regards,

Sergey

0 Likes
Partner
Partner

Sergey Makushinsky написал(а):

...

But anyway - thank you for sharing. It's very interesting.

...

Regrettably I'm not an author of this excellen document

We use somewhat similiar technique in our projects though, so I'm opinionated about it

I see only one problem if you need to use Month as dimension and show what is actual month

I believe that actually it is a problem for traditional, set expression based  point in time analysis. Did your $(eAmount($(vPrevMonth))) work properly at chart with Month dimension?

Meanwhile with model demonstrated in that document it would work.

See How to trend Current Sales vs Prev Sales, using date as dimension? for example.

Luminary
Luminary

Thank Vadim for your kind words.

Agree - I would often tie myself in knots and customer requirements for Point in Time calculations, they're possible although I've found this approach works best in those circumstances.

Richard

0 Likes
Luminary
Luminary

Hi Barry,

Yes for simplistic dashboards with limited requirements for point in time calculations using set analysis can often be a simpler approach.

This calendar does more of the hard graft for calculations like 'Previous Month Comparison' so for today it will return data from the 1st April to 20th April. It also does a lot of work around business days (non weekend / public holiday) allowing you to calculate the business days back in relation to today or perhaps you want the last business day in the month / qtr / year. flags are set up for that also.

The concept isn't new, the post was aimed to keep the code as simple as possible to understand and customise further.

Richard

0 Likes
Luminary
Luminary

You're welcome Marcio,

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2014-05-15 06:00 AM
Updated by: