Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am using below code to get Date_Runrate
LET vCalendarStart = Date#('01/01/2014');
LET vCalendarEnd = YearEnd(today()+5);
LET vCalendarLength = $(#vCalendarEnd) - $(#vCalendarStart) + 1;
Calendar1:
LOAD
Date($(#vCalendarStart) + RecNo()-1) AS "Date_Runrate"
AutoGenerate $(#vCalendarLength);
Calendar2:
LOAD
Date_Runrate,
Year(Date_Runrate) AS Year_Runrate,
Num(Month(Date_Runrate), '00') AS Month_Runrate,
month(weekend(Date_Runrate))& year(weekend(Date_Runrate)) as MonthPeriod_Runrate,
Num(Day(Date_Runrate), '00') AS Day_Runrate,
Num(Week(Date_Runrate), '00') AS Week_Runrate,
weekday(Date_Runrate) as Names_of_day,
date(weekend(Date_Runrate),'MM-YY') as MonthsPeriod_Runrate
resident Calendar1;
drop table Calendar1;
Now i need to find Min and Max of Date_Runrate where MonthPeriod_Runrate will be current month.
How could i do this.Please help me.
Hi Supriya, you can obtain the min and max dates using a resident load with a formatted Today() date:
MaxMinDate:
LOAD Date(Min(Date_Runrate)) as Min_Date_Runrate,
Date(Max(Date_Runrate)) as Max_Date_Runrate
Resident Calendar2 Where MonthsPeriod_Runrate=Text(Date(Today(), 'MM-YY'));
You could use something like this:
...
if(inmonth(today(), Date_Runrate), date(floor(monthstart(Date_Runrate)))) as MinDateCurrentMonth
...
- Marcus
Hi Naveen,
Please find the attached Qlik's Demo App
Please provide the script where you implemented these snippet.
- Marcus
Hi Marcus Sommer ,
This is how i added in middle of script,
LET vCalendarStart = Date#('01/01/2014');
LET vCalendarEnd = YearEnd(today()+5);
LET vCalendarLength = $(#vCalendarEnd) - $(#vCalendarStart) + 1;
Calendar1:
LOAD
Date($(#vCalendarStart) + RecNo()-1) AS "Date_Runrate"
AutoGenerate $(#vCalendarLength);
Calendar2:
LOAD
Date_Runrate,
Year(Date_Runrate) AS Year_Runrate,
Num(Month(Date_Runrate), '00') AS Month_Runrate,
month(weekend(Date_Runrate))& year(weekend(Date_Runrate)) as MonthPeriod_Runrate,
if(inmonth(today(), Date_Runrate), date(floor(monthstart(Date_Runrate)))) as MinDateCurrentMonth,
Num(Day(Date_Runrate), '00') AS Day_Runrate,
Num(Week(Date_Runrate), '00') AS Week_Runrate,
weekday(Date_Runrate) as Names_of_day,
date(weekend(Date_Runrate),'MM-YY') as MonthsPeriod_Runrate
resident Calendar1;
drop table Calendar1;
Here it gives Error in expression: InMonth takes 3-4 parameters
second time i tried with giving at end
if(inmonth(today(), Date_Runrate), date(floor(monthstart(Date_Runrate)))) as MinDateCurrentMonth, here it gives
Script line error:: return
-supriya
Sorry, I thought the third parameter for shift is optional by InMonth but it's not and therefore you need to add:
inmonth(today(), Date_Runrate, 0)
- Marcus
Hi Marcus Sommer ,
I need i was not clear about my query.Please see attached qvf file,In that as it shows,its showing current month and year,according to that there is date_runrate.
In this i want to get 'MIndate' variable as 06-27-2016 and 'Maxdate' variable as 07-31-2016.
Please help me on this.
I couldn't look in your app because I have at the moment no qlik sense available - maybe others could take a look on this. Whereby I don't understand how to you need/get the 06-27-2016 as Mindate?
- Marcus
Hi,
You can run above code displayed in my question in qlikview,and filter for july2016 in MonthPeriod_Runrate.
You can see what i am saying about.
Regards,
Supriya