Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

custom calendar to switch between customer service month and month year

i want to create a calendar that can relate values based on the selection of either service month by customer (e.g. compare values for service months 1-6 between customers) and month year according to a normal calendar (compare values for Q1 2013 for all customers). my desired output is attached. does anyone have any ideas on how i can script this in the load where i would provide the service period starts for all customers along with corresponding month year?

Tags (1)
1 Reply
MVP
MVP

Re: custom calendar to switch between customer service month and month year

I assume you already have a fact table with at least three fields, Customer, Period, and your percentage, right?

So getting the calendar by Month Year should be easy, either by using the Period field or linking to a master calendar

(http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar).

For your service month, specific to a customer, you could find the service start (min Period or a service start date per customer from a separate table), and create a table in your model with fields Customer and ServiceStart.

It might also be good to in addition create a consecutive MonthID, a number that counts the months from a certain point in time (say, January 2000), so we are now in month with MonthID, hmmm, 14*12+3=171.

Then join the ServiceStart to your fact table and create a difference between ServiceStart and current MonthID for your ServiceMonth.