
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calendar Table
Temp:
Load
min("Invoice Date") as MinimumInvDate,
max("Invoice Date") as MaximumInvDate
Resident SalesDetails;
Load
min("CL_Key") as MinBudgetDate,
max("CL_Key") as MaxBudgetDates
Resident Budget;
Load
min("Actual Delivery Date") as MinDelDate,
max("Actual Delivery Date") as MaxDelDate
Resident SalesDetails;
LET varMinDate = Num(Peek('MinimumDate', 0,'Temp'));
LET varMaxDate = Num(Peek('MaximumDate', 0,'Temp'));
LET varToday = num(today());
Drop Table Temp;
//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + RowNo() - 1 AS Num,
Date($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;
//*************** Master Calendar ***************
MasterCalendar:
LOAD
TempDate AS DateKey,
TempDate AS "Invoice Date",
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyeartodate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
I want to select MinimumDate and MaximumDate from line 3-12 there are three different types of dates for min and max , I want to include these dates so that calendar table gives date across all three dimensions instead of just one.
@PrashantSangle I hope you can help
Please Accepts as Solution if it solves your query.
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, Error msg showing that $(varToday) is not populating correctly.
// LET varToday = num(today());
uncomment that variable and try.
if you want to debug your code then comment flag fields.
//inyeartodate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag,
//inyeartodate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag
and then run.
Regards,
Prashant Sangle
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, this is another error, note that the error message has a comma betwen '>>>>>>' and '<<<<<<', telling you where it found the error, there is a comma alone, without the second parameter of the inyeartodate() function.
Follow the instructions give by @PrashantSangle to solve this one.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, you can try with:
LET varMinDate = Num(RangeMin(Peek('MinimumInvDate', 0,'Temp'),Peek('MinBudgetDate', 0,'Temp'),Peek('MinDelDate', 0,'Temp')));
LET varMaxDate= Num(RangeMax(Peek('MaximumInvDate', 0,'Temp'),Peek('MaxBudgetDates', 0,'Temp'),Peek('MaxDelDate', 0,'Temp')));

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
you can try method suggest by Ruben rangemin() and rangemax() work
Or
Concatenate those 3 table with same fieldname and again take resident of temp table named it as finalTemp to find min and max.
And use finalTemp in variable instead of Temp.
Regards,
Prashant
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Since there are no common field already present, should I rename them same and concatenate?
Please Accepts as Solution if it solves your query.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Temp:
Load
min("Invoice Date") as MinInvDate,
max("Invoice Date") as MaxInvDate
Resident SalesDetails
Where not IsNull("Invoice Date")
Order By "Invoice Date" ASC;
Let MinDate1 = Num(Peek('MinInvDate',0,'Temp'));
Let MaxDate1 = Num(Peek('MaxInvDate',0,'Temp'));
Drop Table Temp;
Temp1:
Load
min("CL_Key") as MinBudgetDate,
max("CL_Key") as MaxBudgetDate
Resident Budget;
Let MinDate2 = Num(Peek('MinBudgetDate',0,'Temp1'));
Let MaxDate2 = Num(Peek('MaxBudgetDate',0,'Temp1'));
Drop Table Temp1;
Temp2:
Load
min("Actual Delivery Date") as MinDelDate,
max("Actual Delivery Date") as MaxDelDate
Resident SalesDetails;
Let MinDate3 = Num(Peek('MinDelDate',0,'Temp2'));
Let MaxDate3 = Num(Peek('MaxDelDate',0,'Temp2'));
Drop Table Temp2;
Let varMinDate = Num(RangeMin(Peek('MinInvDate',0,'Temp'),Peek('MinBudgetDate',0,'Temp1'),Peek('MinDelDate',0,'Temp2')));
Let varMaxDate = Num(RangeMax(Peek('MaxInvDate',0,'Temp'),Peek('MaxBudgetDate',0,'Temp1'),Peek('MaxDelDate',0,'Temp2')));
// LET varMinDate = Num(Peek('MinimumDate', 0,'Temp'));
// LET varMaxDate = Num(Peek('MaximumDate', 0,'Temp'));
// LET varToday = num(today());
//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + RowNo() - 1 AS Num,
Date($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;
//*************** Master Calendar ***************
MasterCalendar:
LOAD
TempDate AS DateKey,
TempDate AS "Invoice Date",
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyeartodate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
I have tried this after initial question, in this I am getting Autogenerate Negative Error
Please Accepts as Solution if it solves your query.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes.
Temp:
Load
min("Invoice Date") as MinDate,
max("Invoice Date") as MaxDate
Resident SalesDetails;
Load
min("CL_Key") as MinDate,
max("CL_Key") as MaxDates
Resident Budget;
Load
min("Actual Delivery Date") as MinDate,
max("Actual Delivery Date") as MaxDate
Resident SalesDetails;
FinalTemp:
Load
Min("MinDate") as finalMinDate,
Max("MaxDate") as finalMaxDate,
Resident Temp;
Drop table Temp;
Regards,
Prashant Sangle
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have tried this method also but getting an error.
Please Accepts as Solution if it solves your query.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Temp:
Load
min("Invoice Date") as MinDate,
max("Invoice Date") as MaxDate
Resident SalesDetails;
Concatenate
Load
min("CL_Key") as MinDate,
max("CL_Key") as MaxDate
Resident Budget;
Concatenate
Load
min("Actual Delivery Date") as MinDate,
max("Actual Delivery Date") as MaxDate
Resident SalesDetails;
FinalTemp:
Load
min(MinDate) as varMinDate,
max(MaxDate) as varMaxDate
Resident Temp;
Drop Table Temp;
Error is Autogenerate negative count
Please Accepts as Solution if it solves your query.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- What is value you are getting varMinDate and varMaxDate variable?
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, if you do a drop of each temp table, when you get to the rangemin and rangemax sentences the tabla has alredy been deleted,.
Try doing the drops after you assign the variable.

- « Previous Replies
-
- 1
- 2
- Next Replies »