How to link master calendar dates to your dates in data

[#1]

Hi

My requirement is that i have to indicate if an event had occurred on day. It is like a boolean, either i have data for that day or i do not have data for that day. For this i had to create and use master calendar so that all the dates for a given month are there (irrespective of whether i have the data for that day or not). If I have data for that day I have to mark that data is available (any kind of visualization like bar or line chart is okay) and if i don't have data i could leave it blank by not marking anything.

What I tried:

1. On X-Axis i had dates from my data (not master calendar) and selected Continuous axis which put all the dates (though i don't have data for a day). But the problem is that scroll bar does not appear and x-axis scale is automatically set and i have no control over it.

2. I used master calendar, which puts all the dates on X-axis but i have a problem in linking my data to master calendar dates (both are two different tables and have no link with each other).

Thanks for your support in advance.

[#2]

Thanks for your suggestion Amay. Please see my concern in reply to Jonathan's post. Do you think I can have the field names same and still get around the concern I mentioned.

[#3]

You can try this expression

Count(if(Facts_Dates=Test_Facts_Dates,Facts_Dates))

and in presentation tab don't suppress zero values.

--

amay

[MK_QSL]

Facts:

LOAD  Date(Date#(@1,'YYYY-MM-DD')) as Facts_Dates

FROM

[..\2. Manish\Community\157310.xls]

(biff, no labels, table is Sheet1$);

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

               min([Facts_Dates]) as minDate, 

               max([Facts_Dates]) as maxDate 

Resident Facts; 

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 

               Date(TempDate,'YYYY-MM-DD') AS Facts_Dates, 

               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;

[jpenuliar]

Think of the Master Calendar as a Dimension Table where a specific Date is linked only to the matching Date in the facts Tables, similar to vlookup. If I have a specific Date from Facts table (05/03/2015) I can return the Week Number equivalent from the Master calendar . I do not believe you mixed the dates since you did not join nor concatenate the tables,

[#6]

Hi Sandeep,

You change your date into number then link with master .

like  num(Date) as Date .

same in your transaction table.

the result will be perfect.

Regards

Vimlesh

[#7]

Having a field name same in two tables, Qlikview automatically joins the tables. Result is attached in reply to this original post. 

[#8]

Hi All

I changed the field name in Master Calendar to Facts_Dates. The result is attached. Notice that though i don't have Facts_Dates on 8th,9th and 16th from original data, it shows bars on those dates as well, misleading that data is available on those dates.

[jpenuliar]

I see your point,  try the below:

Facts:

LOAD
1 as Counter,
@1 as Facts_Dates
FROM

(biff, no labels);

Instead of Counting Fact_Dates,  Count(Counter)

[sunny_talwar]

Would you be able to post the script for the table which contains the your date column and the script for your master calendar here. Not really sure what got mixed up?

Best,

Sunny

[#11]

PFA...

HTH

--

Amay