Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

pauledrich
Contributor

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

Re: Help - last day of Month

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

3 Replies

Re: Help - last day of Month

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

pauledrich
Contributor

Re: Help - last day of Month

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

pauledrich
Contributor

Re: Help - last day of Month

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

Community Browser