# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

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.

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.

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

Contributor

## Re: Help - last day of Month

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

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:-

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