Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
A representative sample qvw would have been a better choice to help us help you.
The variable dates should be in the same format as your field format
check this page
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
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
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
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