Master calendar not working

[evie]

I recently changed the .qvd location and updated my script with the new path. All of the fields are returning correctly but the fields I created in my master calendar are not working as filters on my sheet.

The filters I am specifically looking at are IsCurrentMTD and Month-Year. Say I select 1, or yes, for IsCurrentMTD, the data in my table on my sheet does not change but the Month-Year filter does show I am looking at only the current. Vice versa, I select a month/year in the Month-Year filter and the IsCurrentMTD will update but the data is not filtered by the month I select, nothing changes. (Attached images of this)

Here is my script:

Tmp1:
Load
   Name, 
   Date, 
   Department
From [data.qvd]

MinMax:

LOAD

min(Date) as minDate,

max(Date) as maxDate

Resident Tmp1;

Let vMin=Peek('minDate',0,'MinMax');

Let vMax=Peek('maxDate',0,'MinMax');

Drop Table MinMax;

TempCal:

Load

Date($(vMin) + RowNo() - 1) as TempDate

AutoGenerate $(vMax) - $(vMin) + 1;

MasterCalendar:

Load

   TempDate as [Action Date*],

   Day(TempDate) as Day,

   week(TempDate) As Week,

   WeekDay(TempDate) as [Week Day],

   Num(Month(TempDate)) as Month,

   Year(TempDate) as Year,

   Dual(Month(TempDate) & '-' & Year(TempDate),

   MonthStart(TempDate)) as [Month-Year],

   Dual('Q' & ceil(Month(TempDate)/3),ceil(Month(TempDate)

/3)) as Quarter,

   InMonthToDate (TempDate, today(), 0)* (-1) as IsCurrentMTD,

   InMonthToDate (TempDate, today(), -1)* (-1) as IsLastMTD,

   InMonthToDate (TempDate, today(), -13)* (-1) as IsLastYearMTD,

   InWeekToDate (TempDate, today(), 0) * (-1) as IsCurrentWTD,

   InWeekToDate (TempDate, today(), -53) * (-1) as IsLastYearWTD,

   InYearToDate (TempDate, today(), 0) * (-1) as IsCurrentYTD,

   InYearToDate (TempDate, today(), -1) * (-1) as IsLastYTD,

   InYearToDate (TempDate, today(), 0, 10) * (-1) as IsFiscalYTD,

   (TempDate = today()-1)* (-1) as PreviousDay

Resident TempCal;

Drop Table TempCal;

As a temporary fix, I created these fields in the first table but I would really like to have the master calendar in my script. Can anyone help with this?

[Oleg_Troyansky]

Hi Evie,

Ideally, if you could post a sample app, we could look into it and tell you what's wrong. In the absence of that, we can only guess. To troubleshoot, I'd start from examining the data:

- what's the range of the dates in the Fact table?

- were the min and max variables populated properly?

- add a Table with all calendar fields and verify visually that all the flags are populated as expected.

However, from your description of the symptoms, I'd guess that you might have measures with Set Analysis conditions that could be disregarding your selections - either all selections (for example, {1} ) or specifically the Calendar selections. If the data looks right, then I'd examine the particular formulas in your charts.

Another possible scenario is if Alternate States are used in your Apps. If your sheet objects are assigned to an Alternate State, then your selections in the default state wouldn't matter. However, in this case moving these flags to the fact table wouldn't fix the problem...

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

[evie]

Hi, 

My data set is very large and has very sensitive information in it.

I placed those fields into my table and they are not populating correctly. For one row, it’ll repeat IsCurrentMTD twice, once for 1 and once for 0. Same thing for Month-Year, but a row for every month that is available in the data. 

If this doesn’t give helpful information, I can work on turning it into sample data in the meantime. 

[Oleg_Troyansky · Accepted solution]

Is this Date field in the table the same as the [Action Date] in the master calendar, or is it a different field? It feels like your date fields are not associated with the Master Calendar properly, hence the same date is "associated" with multiple Months. Check your data model and data associations in the model.

If that doesn't solve the issue, then - yes, build a sample app that we could review and analyze.

[evie]

I realized that shortly after my last response. You’re right, there was no relation between the two tables. I was able to get that fixed and working properly  

Thank you!