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

Min/Max field values not working in Master Calendar

b_garside — Fri, 08 Mar 2013 17:22:44 GMT

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 ?

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 1
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,

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

Gysbert_Wassenaar — Fri, 08 Mar 2013 17:30:10 GMT

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');

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

b_garside — Fri, 08 Mar 2013 17:47:05 GMT

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?

Max / Min from variables

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

Gysbert_Wassenaar — Fri, 08 Mar 2013 17:54:22 GMT

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.

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

er_mohit — Fri, 08 Mar 2013 17:59:36 GMT

hiiii

write this

Load

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

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

b_garside — Fri, 08 Mar 2013 18:08:17 GMT

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.

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

b_garside — Fri, 08 Mar 2013 18:13:19 GMT

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().