Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Master II
Master II

Previous month last day

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

stalwar1neelamsaroha1575

Thanks.

1 Solution

Accepted Solutions
Highlighted

Re: Previous month last day

For quarter, ignore selection in Quarter field (in fact add all the date and time related fields where you might make a selection)

Count({<Date ={"$(=Date(MonthStart(Min(Date))-1, 'M/D/YYYY'))"}, Year, Month, Quarter>} DISTINCT ID)

View solution in original post

17 Replies
Highlighted
Master III
Master III

Re: Previous month last day

Use MonthEnd ...

Highlighted

Re: Previous month last day

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

Highlighted
Master II
Master II

Re: Previous month last day

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

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

Highlighted
Master II
Master II

Re: Previous month last day

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

Highlighted

Re: Previous month last day

Awesome

Highlighted
Master III
Master III

Re: Previous month last day

Please share some sample data with expected output.

Highlighted
Master III
Master III

Re: Previous month last day

Highlighted
Master II
Master II

Re: Previous month last day

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.

Highlighted

Re: Previous month last day

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?