Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calendar/Slider object with Master Calendar not filtering data

Hi all,

i am creating a report that needs to be filtered by min date / max date based on a field SBISOEnterDate.

I have created the master calendar, and the min/max variables seem to be working but i am not getting any filtering on my results...

anyone have any ideas to offer? Thanks in advance!! Fab...

Here are some screen shots:

variables.JPG

Tables.JPG

MaxSOEnterDate.JPG

MinSOEnterDate.JPG

Script is as follows:

QuartersMap:

MAPPING LOAD * Inline [

Month, Q

1, Q3

2, Q3

3, Q3

4, Q4

5, Q4

6, Q4

7, Q1

8, Q1

9, Q1

10, Q2

11, Q2

12, Q2

];

Temp: 

Load 

               min(SBISOEnterDate) as minDate, 

               max(SBISOEnterDate) as maxDate 

Resident SBISOrder; 

 

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 

               TempDate AS SBISOEnterDate, 

               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; 

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Hi all,

I was able to resolve my query by eliminating the master calendar and using variables with triggers as per this video:

Selecting Arbitrary Date Ranges - YouTube

Thanks to all for your contribution

Fab

View solution in original post

6 Replies
tresesco
MVP
MVP

A representative sample qvw would have been a better choice to help us help you.

vinieme12
Champion III
Champion III

The variable dates should be in the same format as your field format

check this page

Dates in Set Analysis

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

Hi Tresesco B,

I see your point, however last time i tried to do that it took me half a day to setup a sample qvw to demonstrate my issue...

I'm pulling Data out of an ODBC database and my table model for any one report includes multiple tables so I have to make signifcant changes to my script in order for the qvw to work...

Having said that, once i invested the time someone did get back to me with an answer that resolved the issue... so based on that experience I keep that as a last resort, considering how time-consuming that exercise was. 

The fact is that there must be an easier way than what I did last time  but probably that I'm too new to Qlikview to understand how to generate a sample quickly...

hopefully someone can help me based on the information provided.

Any help is appreciated anyways... just figuring out one problem at a time...

thanks for your comment anyway.

Cheers Fab

Anonymous
Not applicable
Author

Thanks Vineeth,

I see your point, this is the first time I'm using the master calendar and the variables defining calendar ranges...

I have looked at the link you sent and a few others in the knowledgebase but still cannot understand how to resolve this issue...

In my Data source, the field appears to be the correct Date format (DD/MM/YYYY)mastercalendartablepreview.JPG

however my variables are picking up a numeric format as per my previous screenshot:

Variables.JPG

How do I go about changing my script so that varMinDate and varMaxDate retain the Date formatting?

Having said that, these variables are set as part of the master calendar script, which I found in a qlikview tutorial.

Should I be creating new variables within the document (not script) for my calendar/slider objects? I have also tried to do this but haven't been able to get it to work yet... the new variables also seem to convert the field in a numerical format.

hope this makes sense...

thanks in advance for your help...

Fab

Anonymous
Not applicable
Author

Hi all,

I was able to resolve my query by eliminating the master calendar and using variables with triggers as per this video:

Selecting Arbitrary Date Ranges - YouTube

Thanks to all for your contribution

Fab

Anonymous
Not applicable
Author

Thanks for posting this link Fabrizio, my calendar object wasn't filtering because I hadn't added a trigger.  It's working now.  Thanks again.  Kathleen