Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
b_garside
Valued Contributor

Min/Max field values not working in Master Calendar

I have a date value that I pulled the MaxRevMonth & MinRevMonth values from a resident table and want to use them in my Master Calendar.

For some reason it errors out saying "Field not found". Seems like a simple thing, what I'm I missing ?

errr.jpg

ForecastRev:
LOAD date(floor("Report EOM")) as [Report EOM Rev] ,
date(floor(ReportRundate)) as [ReportRundate_Rev],
Project,
date(floor(RevMonth)) as RevMonth,
//date(floor(Max(RevMonth))) as MaxRevMonth,
    //date(floor(Min(RevMonth))) as MinRevMonth,
    RevAmt,
RevType,
WorkLocation,
BacklogType as [BacklogType_Rev];
//group by Project;
SQL SELECT "Report EOM",
ReportRundate,
Project,
RevMonth,
RevAmt,
RevType,
WorkLocation,
BacklogType
FROM CorpReporting.MonthlyBacklogForecastRev;

//Concatenate (ForecastRev)

MaxMinRevMonth:
Load

date(floor(Max(RevMonth))) as MaxRevMonth,
date(floor(Min(RevMonth))) as MinRevMonth

Resident ForecastRev;

//****************************************************** New Tab

LET vDateMin = MinRevMonth;              
LET vDateMax = MaxRevMonth;         

TempCalendar: 
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber
Date($(vDateMin) + RowNo() - 1) AS TempDate 
AUTOGENERATE
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax)

Calendar: 
LOAD
Date(TempDate) AS CalendarDate

// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth
WeekDay(TempDate) AS CalendarDayName
Week(TempDate) AS CalendarWeekOfYear
Month(TempDate) AS CalendarMonthName
'Q' &
Ceil(Month(TempDate)/3) AS CalendarQuarter
Year(TempDate) AS CalendarYear,  

Tags (2)
6 Replies

Re: Min/Max field values not working in Master Calendar

The peek function is necessary to be able to  ..well, peek into a table.

Change the lines where you create the variables to:

LET vDateMin = peek('MinRevMonth');              
LET vDateMax = peek('MaxRevMonth');   



talk is cheap, supply exceeds demand
b_garside
Valued Contributor

Re: Min/Max field values not working in Master Calendar

For some reason it only output one row for calendar withese dates even though its uppose to 2021.  That date is malformedI guess.

I typically shown them in MM/DD/YYY  any ideas. Yet when I drop it into a text box it shows another date range?

2013-03-08_124117.png

Max / Min from variables

2013-03-08_124507.png

Re: Min/Max field values not working in Master Calendar

If RevMonth doesn't contain a date but a month then you'll start at the date with the numeric value of the month. That's probably 1, which is date 31-12-1899 and your calendar will end at most 11 days later.


talk is cheap, supply exceeds demand
er_mohit
Honored Contributor II

Re: Min/Max field values not working in Master Calendar

hiiii

write this

Load

date(floor(Max(Date(RevMonth),MM/DD/YYY))) as MaxRevMonth,
date(floor(Min(Date(RevMonth),MM/DD/YYY))) as MinRevMonth

b_garside
Valued Contributor

Re: Min/Max field values not working in Master Calendar

Didn't seem to help. But I understand the suggestion. I looked at other Calendars and the code seems to be correct.

The peak based variables are outputting something totaly different than the source.  Even after the date formatting.

2013-03-08_130635.png

b_garside
Valued Contributor

Re: Min/Max field values not working in Master Calendar

Makes sense. I need to rename the field thrn. Not sure why its called RevMonth when it has a full date. Infact i remvoed the time stamp using Floor().

Community Browser