Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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?

1 Reply
swuehl
MVP
MVP

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.