How to change assigned synthetic key

[Ruth_O]

I have recently taken over the ownership on a set of QlikView dashboards that run from a master calendar script with EngineDate - which QlikView has assigned a synthetic key to.

Load
Min(EngineDate) as minDate,
Max(EngineDate) as maxDate

This is fine for adding multi-box filters to graphs that have related engine data in them, however, I want to add multi box filters that don't contain EngineDate information, these additional graphs represent something different.

Is there an easy way of doing this?

Thanks,

Ruth

[dplr-rn]

Do you mean you have multiple date fields? and you want a single way of filtering.

check canonical date concept.

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

[Ruth_O]

Hi,

I have multiple graphs, which I want to filter the data on, year/month/quarter etc.  However, how the multi-box filter is set-up at the moment, I can only filter graphs that align to anything related to Engine Date, as this is the date range the master calendar is pointing at.

For the other graphs, that don't contain any engine data, I want to filter on different months/years, but the multi-box option won't let me select anything else.  I think I need to set-up a separate master calendar so I can apply filters to these other graphs within the dashboard view?

Thanks for your help,

Ruth

[dplr-rn]

That is the canonical date concept.

once you implement you could set the filters from the CanonicalCalendar.

You will need to setup your  visualizations appropriately as well.

check the link i shared it goes in good detail

[Ruth_O]

Hi,

Thanks for getting back to me

I have had a look at your previous post in relation to a CanonicalCalendar.  I can definitely see how this would be really beneficial.  However, when looking at what has previously been set-up in the QlikView dashboard I have taken over, there is no table for the master calendar that is used.  It's loaded purely from script using Engine Date, this only allows me to filter graphs and views where engine score data is used, script is below:

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
 
Load
Min(EngineDate) as minDate,
Max(EngineDate) as maxDate

Resident EngineScore;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
               $(varMinDate) + Iterno()-1 As Num,
               Date($(varMinDate) + IterNo() - 1) as TempDate
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:

Load
               Num(TempDate) AS EngineDate,
               week(TempDate) As Week,
               Year(TempDate) As Year,
               Month(TempDate) As Month,
               Day(TempDate) As Day,
               YeartoDate(TempDate)*-1 as CurYTDFlag,
               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
               WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Is there any way I can amend the above to a canonical date concept?

Thanks for your help,

Ruth

[Brett_Bleess]

The other link you likely need is the following:

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

Here is the base URL in case you need to do further searching in the future:

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

Regards,
Brett