Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Partner
Partner

How to show data for previous month in Qlik Sense

Hi,

I am trying to show only last month data.

Example:

Today's date is 10/11/18, which means we are in October. Because it is October, I want it to show me all the data records for the month of September.

Currently my formula is only for the past 30 days, but this is not ideal since it takes in today's date and only subtracts 30.

Count({< [Closed Date] = {"=[Closed Date] >= Today() - 30"} >} [Record ID])

Thanks

9 Replies
Partner
Partner

Re: How to show data for previous month in Qlik Sense

Do you have a master calendar setup on Closed date?

if/when you do use set analysis something like Month= {"Month(today())"}

Partner
Partner

Re: How to show data for previous month in Qlik Sense

No, I don't. I just have the autogenerated calendar options.

Partner
Partner

Re: How to show data for previous month in Qlik Sense

Autogenerated one should have a month field too usually .

If not use the master calendar script from

Better Calendar Scripts | Qlikview Cookbook

Partner
Partner

Re: How to show data for previous month in Qlik Sense

It does. So what would you reccommend the set analysis to look like?

Partner
Partner

Re: How to show data for previous month in Qlik Sense

Use the addMonths, MonthStart and MonthEnd Functions, it'll be sthing like this:

sum( {<Month,Date={">=$(=MonthStart(AddMonths(max(Date),-1)))<=$(=MonthEnd(AddMonths(max(Date),-1)))"}>}Measure)

What this does; if u select 23/09/2018 for example; it will show u the data for

the dates between 01/08/2018 and 31/08/2018

Hope this helps

Partner
Partner

Re: How to show data for previous month in Qlik Sense

Something like below

Month={"$(=Month(Today()) )"}, Year={"$(=Year(Today()) )"}

or

[Closed Date] ={">=$(=MonthStart(Today()))"}>}

Partner
Partner

Re: How to show data for previous month in Qlik Sense

Any luck?

close the thread if it helped

Highlighted
Partner
Partner

Re: How to show data for previous month in Qlik Sense

I used the formula but it doesn;t work. It shows me "0" even though the count should be 10.

I added in the auto generated month into "Month" and then I added Record ID to "measure".

sum( {<[Date/Time Closed.autoCalendar.Month],Date={">=$(=MonthStart(AddMonths(max(Date),-1)))<=$(=MonthEnd(AddMonths(max(Date),-1)))"}>}[Record ID])

Re: How to show data for previous month in Qlik Sense

How about this?

Count({< [Closed Date] = {"$('>=' & Date(Max([Closed Date]) - 30))"} >} [Record ID])


Or

Count({< [Closed Date] = {"$('>=' & Date(Max(TOTAL [Closed Date]) - 30))"} >} [Record ID])

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)