Getting min and max date of Date_Runrate

[berryandcherry6]

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.

[marcus_sommer]

You could use something like this:

...

if(inmonth(today(), Date_Runrate), date(floor(monthstart(Date_Runrate)))) as MinDateCurrentMonth

...

- Marcus

[krishna20]

Hi Naveen,

Please find the attached Qlik's Demo App

[berryandcherry6]

Hi marcus_sommer  ,

Thanks for your reply !

if i run this i am getting Script line error:: return

[marcus_sommer]

Please provide the script where you implemented these snippet.

- Marcus

[berryandcherry6]

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

[marcus_sommer]

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

[berryandcherry6]

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.

[marcus_sommer]

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

[berryandcherry6]

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