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.
pauledrich
Contributor

Last day of month Value

Hi, I have an issue which did return the value I needed however- when the last day of the month falls on a Sunday the return value is zero - how can I amend the script below to return the previous days value when the value is zero and also retain the LastDay returned value?

The script is:-

Load
  
Location,

  
Value,

  
Date (floor(Date)),'DD/MM/YYYY' as LastDate,

  
Date,

  
Day (Date) as Day,

  
MonthEnd (Date)as MonthEnd,

  
MonthStart (Date) as FirstDay,

  
MonthName (Date) as Month,

  
Month (Date) as Month1,

  
Year (Date) as Year,

  
IF(Floor(Date) = Floor(MonthEnd(Date)),value,0) as LastDay;

From .........

Thank you if anyone can help

1 Reply
Sokkorn
Honored Contributor

Re: Last day of month Value

Hi,

First of all you need to set day name in script like below

Untitled.png

Then use script below

Load

   Location,

   Value,

   Date (floor(Date)),'DD/MM/YYYY' as LastDate,

   Date,

   Day (Date) as Day,

   MonthEnd (Date)as MonthEnd,

   MonthStart (Date) as FirstDay,

   MonthName (Date) as Month,

   Month (Date) as Month1,

   Year (Date) as Year,

   IF(Floor(Date) = Floor(MonthEnd(Date)),value,0) as LastDay,

  

   If(WeekDay(MonthEnd(Date))='Sun',0,Value)                        AS [LastDay2],

   If(WeekDay(MonthEnd(Date))='Sun',Previous(Value),Value)    AS [LastDay3];

From .........

Regards,

Sokkorn

Community Browser