topic Re: Previous month last date in QlikView

Previous month last date

U_Waqar — Mon, 04 Jul 2022 09:35:55 GMT

Hi Experts,
I am using following expression to get count from start of last month to end of last month but not getting count of last day only i.e 30/06/2022:
Expression:
count(
{<
REGISTRATION_DATE = {">=$(=MonthStart(Addmonths(Max(REGISTRATION_DATE),-1)) <=$(=Monthend(Addmonths(Max(REGISTRATION_DATE),-1)) )"}
>}
DISTINCT ID)
DateFormat is DD/MM/YYYY

Please advise.

Many thanks

Re: Previous month last date

Or — Mon, 04 Jul 2022 10:30:39 GMT

You're checking against MonthStart, which is the first of the month, so I'm not sure why you'd expect to only get the last day of the month here.

Re: Previous month last date

U_Waqar — Mon, 04 Jul 2022 10:34:09 GMT

I want to get for complete last month but count is not showing for only last day of previous month i.e 30/06/2022.

Re: Previous month last date

Or — Mon, 04 Jul 2022 10:39:15 GMT

Then you shouldn't be using MonthStart()..? Perhaps Floor(MonthEnd(Whatever)) instead?

Re: Previous month last date

U_Waqar — Mon, 04 Jul 2022 12:25:00 GMT

Requirement is to calculate IDs from start of the previous month to end of previous the previous month.
Can you please tell what should I use?

Thanks

Re: Previous month last date

Or — Mon, 04 Jul 2022 13:00:35 GMT

You might be having issues with the time portion of the datetime (EndMonth returns e.g. 30/06/2022 23:59:59.999), have you tried the same thing but with that date floored? Does the actual formula return the expected value when used in e.g. a text object?

As an aside, if it's feasible for you, I'd suggest having a Registration Month field (Monthname([Registration Date])) and then you can just compare months instead of date ranges, which is easier to work with and cleaner to read.