Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pauledrich
Creator
Creator

Help - last day of Month

Hi - I have read various solutions to this problem but have now exhausted every avenue. I have 3 colums "Location, Value, Date", I want to return value for the last date of each month.

My script is

"Load

Location,
  
Value,
   
Date (floor(Date)),'DD/MM/YYYY' as Date,
   
Date,
  
Day (Date) as Day,
  
MonthEnd (Date)as MonthEnd,
  
MonthName (Date) as Month,
  
Month (Date) as Month1,
  
Year (Date) as Year,
  
IF(Date(SummaryDate,'DD/MM/YYYY') = Date(Floor(MonthEnd(SummaryDate)),'DD/MM/YYYY'),1,0) as LastDay; "

I was hoping that the above would return a value of 1 but it is only returning a value of 0?Any solutions would really help. many thanks.

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

     Try this.

Load

   Location,
   Value,
   Date (floor(Date)),'DD/MM/YYYY' as Date,
   Date,
   Day (Date) as Day,
   MonthEnd (Date)as MonthEnd,
   MonthName (Date) as Month,
   Month (Date) as Month1,
   Year (Date) as Year,
   IF(Floor(SummaryDate) = Floor(MonthEnd(SummaryDate)),Value,0) as LastDay
From ......

     I hope SummaryDate is coming from same table.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

3 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

     Try this.

Load

   Location,
   Value,
   Date (floor(Date)),'DD/MM/YYYY' as Date,
   Date,
   Day (Date) as Day,
   MonthEnd (Date)as MonthEnd,
   MonthName (Date) as Month,
   Month (Date) as Month1,
   Year (Date) as Year,
   IF(Floor(SummaryDate) = Floor(MonthEnd(SummaryDate)),Value,0) as LastDay
From ......

     I hope SummaryDate is coming from same table.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
pauledrich
Creator
Creator
Author

Thank you - very helpfull I now see where I was going wrong. Much appreciated.

pauledrich
Creator
Creator
Author

Hi, I have an issue - when the last day of the month falls on a Sunday the return value is zero - how can I amend the script to return the previous days value when the value is zero?

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