Merging two calendars

[#1]

Hello,

I working with ~20 years worth of data and my analysis is based on YearMonth calendar:

let vToday = num(today());

let vStart = num(yearStart(vToday - 365*20));

// Generate 20 years worth of Dates:

TempCalendar:

load

AddMonths($(vStart), RowNo() - 1) as Date

autogenerate (240);

Calendar:

load

*,

Year & '/' & Quarter as YearQuarter

;

load

Date,

date(Date, 'MMM-YYYY') as YearMonth,

month(Date) as Month,

year(Date) as Year,

'Q' & ceil(month(Date)/3) as Quarter

resident

TempCalendar

;

At the same time I need to analyze the last 60 days from today's date with more details and I need another calendar:

let vDaysStart = num(vToday - 60); // just 60 days

TempDaysCalendar:

load

date($(vDaysStart) + RowNo() - 1) as [Full Date]

autogenerate (60) // the last 60 days back

;

And I need to join these calendars together for some of the reports. What's the best way to do so?

Please advice.

Regards,

Vladimir

[Miguel_Angel_Baeyens · Accepted solution]

Hello Vladimir,

There are several ways to do that. My suggestion is to build a Master Calendar, in which you can calculate any field related to time and date you want to use further in your charts and reports.

There are likewise a lot of posts in the forum on how to get accomplished such calendar, but you can use this. For those last 60 days you need to analyze, you can create in that Master Calendar i. e.: a Flag field, that matches your criteria

If(Date(MasterDateField) < Date(num(vToday - 60), 1) AS Last60Flag

and then use it in a set analysis kind of

Sum({< Last60Flag = {1} >} Sales)

Hope it helps!

[#3]

Hi Miguel!

Give me some solution for me here!

http://community.qlik.com/forums/t/26632.aspx

Thanks and Regards,

Rikab

[#4]

Miguel,

Thanks for reply. It is a good solution, but I was planning to avoid creating a large Master calendar for all days during these years. I really need a days details for the last 60-90 days only and I would like to save some memory and do not generate the rest of the days I will not need.

I will need YearMonth, Quarters fields for the rest of the calendar, so I would need some kind of "Mix" solution.

Is it possible to do so?

I really appreciate your help.

Regards,

Vladimir

[Miguel_Angel_Baeyens]

Hello Vladimir,

You can write a script where you only lead in a temp file the last 60 days. Then create the calendar from that resident table, dropping the temp table after doing that, so you will have your fact table and your last 60 days calendar with all relevant fields for your reports.

[#6]

Thanks, Miguel!

Best regards,

VK