Previous month last day

[MK9885]

Hi,

I've a requirement to count the values from previous month last day.

Ex:

If I select Year 2015>Month Sep> I should count values from Aug-31st

If I select Year 2015>Quarter 2> I should count count values from Previous Quarter (Q1), Last Month of Q1(March) and last day in March

If I select Year 2015> I should get counts for Year 2014, Dec 31st

And all these scenarios are incorporated in single expression, it would be a single expression.

Individually my expression works

I can get previous month counts but can't get previous month last date.

Previous Month:

Count({< [Year Month Num] = {"$(=Date(Date#(Max([Year Month Num]),'YYYYMM')-1,'YYYYMM'))"}, Month = , [Month Year] = , Year =>}

DISTINCT Dimension)

Previous Year, Max Month:

Count({<Num_Month={$(=Max(Num_Month))}, LastMonthFlag={1} >}

DISTINCT Dimension))

But Last Day is not working, always gives me 0 if I give

Count({< Month={"=$(vPriorMonth)"},Day = {$(=Max(Day))} >}

DISTINCT Dimension)

But in text box it seem to work

vPriorMonth is Month(addmonths(max(Date),-1))

If somebody could help??

stalwar1‌ neelamsaroha1575‌

Thanks.

[prma7799]

Use MonthEnd ...

[sunny_talwar]

Would you be able to provide some same data to look at?

[MK9885]

Trust me I tried everything available in community...
Monthend, variables etc

Both in script and frontend but doesn't seem to work

[MK9885]

I'll try to make some dummy data and upload here....@ Sunny

[sunny_talwar]

Awesome

[prma7799]

Please share some sample data with expected output.

[prma7799]

[MK9885]

I modified the QVW from this community thread to adjust as per my req.

QlikView App: Set Analysis - Prior Period Comparison

Please find the attachment.

In above thread the user explains how to get previous month also max of day but doesn't seem to work in an expression.

If you see in sheet1 I'm using

count({<Month={"=$(vPriorMonth)"},Date ={"$(=Monthend(Max(Date),-1))"} >}

distinct ID)

Which should give me ID counts for selected month, max day but it always gives me 0

I hope this would be sufficient...

Here I'd be trying to count the Distinct ID's in a given month only for last day in that month

Thanks.

[sunny_talwar]

If I select Year 2015>Month Sep> I should count values from Aug-31st

If I select Year 2015>Quarter 2> I should count count values from Previous Quarter (Q1), Last Month of Q1(March) and last day in March

If I select Year 2015> I should get counts for Year 2014, Dec 31st

You only mentioned from condition, what is the to condition here?

If you select Year 2015 and Month Sep, you will count from Aug-31st till what date? and similarly for other selections?