topic Re: Calendar/Slider object with Master Calendar not filtering data in QlikView

Calendar/Slider object with Master Calendar not filtering data

Anonymous — Wed, 03 May 2017 07:02:00 GMT

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:

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;

Re: Calendar/Slider object with Master Calendar not filtering data

tresB — Wed, 03 May 2017 07:14:52 GMT

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

Re: Calendar/Slider object with Master Calendar not filtering data

vinieme12 — Wed, 03 May 2017 07:20:35 GMT

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

check this page

Dates in Set Analysis

Re: Calendar/Slider object with Master Calendar not filtering data

Anonymous — Fri, 05 May 2017 00:59:59 GMT

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

Re: Calendar/Slider object with Master Calendar not filtering data

Anonymous — Fri, 05 May 2017 02:07:35 GMT

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)

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

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

Re: Calendar/Slider object with Master Calendar not filtering data

Anonymous — Wed, 10 May 2017 06:58:31 GMT

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

Re: Calendar/Slider object with Master Calendar not filtering data

Anonymous — Thu, 21 Dec 2017 14:11:52 GMT

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