Help me on my issue

[devaraj28]

Hi All,

i have four Date columns like        Offer_StartDate

                                                                    Offer_EndDate 

                                                                    Calendar_StartDate

                                                                    Calendar_EndDate.

Offer_StartDate & offer_EndDate are  in T1 table 

Calendar_Startdate & Calendare_EndDate are in T2 table 

i have created one Calendar_StartDate  calendar object  And one Calendar_EndDate calendar object

As per my requirement, i want show the Offer_StartDate and Offrer_EndDate with in the date rang which i select in Calendar_StartDate and Calendar_EndDate, but issue is "Calendar_Startdate & Calendar_EndDate" column table is independent table(isolated table). can u help me how the apply the triggers to make it work this.

Thanks in Advance.

[Vertig0]

Hi Devaraj28,

I suggest to abandon the Calendar_StartDate and Calendar_EndDate calendar objects completely and create a Master Calendar instead.

Basically you have to go through the following steps:

1. Acquire range 

2. Generate temporary calendar

3. Generate master calendar

Since I don't have your data model details, I will give you an example with a table ''Megawatt_hoursByDay'' with production dates ('ProdDate' column). Look at the logic and replace with your own data.

In details:

Step1. Ascertain the date range required

StartAndEndDates:
LOAD
MIN(ProdDate) AS FirstProdDate,
MAX(ProdDate) AS LastProdDate
RESIDENT Megawatt_hoursByDay;

// Store the start and end dates as variables:
LET vFirstDate = NUM(PEEK('FirstProdDate', 0, 'StartAndEndDates'));
LET vLastDate = NUM(PEEK('LastProdDate', 0, 'StartAndEndDates'));

Step2. Generate a temporary calendar, using the date range boundaries acquired
TempCal:
LOAD
DATE($(vFirstDate) + ROWNO() -1) AS TempDate
AUTOGENERATE
$(vLastDate) - $(vFirstDate) + 1;

3. Generate master calendar

MasterCalendar:
LOAD
TempDate AS OrderDate,
WEEK(TempDate) AS Week,
YEAR(TempDate) AS Year,
MONTH(TempDate) AS Month,
DAY(TempDate) AS Day,
WEEKDAY (TempDate) AS Weekday,
'Q' & CEIL(MONTH(TempDate) / 3) AS Quarter,
DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,
WEEK(TempDate) & '-' & YEAR(TempDate) AS WeekYear
RESIDENT TemporaryCalendar;
DROP TABLE TemporaryCalendar; 

I hope this is helpful!

Regards,

[Vertig0]

P.S. Actually you can still utilize calendar objects if that is the requirement. Just use the aliases from the Master Calendar.

[devaraj28]

Hi,

Thanks for your replay, i have attached the data model screenshot, i have tried to apply triggers like  as mentioned in screenshot. So let me know i am doing in correct way or not ?

[Brett_Bleess]

Just adding a couple of Design Blog posts for you that may prove helpful in getting things sorted out in relation to what the partner suggested...

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

Here is the base URL for the Design Blog area in case you want to do some further searching on your own:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Hopefully this gets you the rest of the way with things or explains them such that you can sort out what you need to do in order to get things working for your use case.

Regards,
Brett