Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
Creator

## how to automatically filter previous month and year in edit expression in qlik sense?

Good Day,

I need to filter previous month in edit expression (automatically change every month), so that data will be display for November 2019 only if I run the app this month.

this is my sample measures in edit expression:

sum({<STATUS = {'A'},NPA_STATUS = {'REGULAR'}, MONTHEND = {'NOV-2019'}>} BALANCES)

Tank you

Labels (3)

• ### Script Logic

13 Replies
Partner - Creator II

Like this

sum({<STATUS = {'A'},NPA_STATUS = {'REGULAR'}, MONTHEND = {"\$(=AddMonths(Today(),-1))"}>} BALANCES)

@DavidM I would rather recommend this

sum({<STATUS = {'A'},NPA_STATUS = {'REGULAR'}, MONTHEND = {"\$(=AddMonths(Max(MONTHEND),-1))"}>} BALANCES)

Or, even simplest way

sum({<STATUS = {'A'},NPA_STATUS = {'REGULAR'}, MONTHEND = {"\$(=Max(MONTHEND-1)))"}>} BALANCES)

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)
Partner - Creator II

@Anil_Babu_Samineniyes, I was thinking the same, however we don't know if there are no future dates in data model and in that case it wouldn't work

Partner - Specialist III

I would suggest a rather different approach.

I would create a flag in Master calendar for Previous month and that easily apply to formulas like:

sum({<STATUS = {'A'},NPA_STATUS = {'REGULAR'}, f_PM= {1}>} BALANCES)

Former Employee

Kristel, just wanted to check to be sure you circled back to your post, as you have received multiple replies but nothing has been marked using the Accept as Solution button, please be sure to do that if any of the posts gave you the information you needed to get your use case working.  This gives credit to the poster and lets other Community Members know what solution worked for you, so again, be sure to return to the thread and use the Accept as Solution button on the post(s) that helped you solve your use case problem.  If you are still working upon things, please leave an update post letting folks know what you still need.

Here is a link to the Design Blog area as well, that may prove useful going forward, there are hundreds of how-to posts in this area, quite a few regarding set analysis related topics:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Creator
Author

Hi,

Apology for late response as I was on leave due to holidays season.

I will mark solve once mentioned formula works.

Thank you

Creator
Author

Suggested does not work.

I user below query to get MONTHEND column.

Can we get the Max value for above mentioned query?

Thanks

Creator
Author

Hi @DavidM

Suggested does not work.

I used below query to get MONTHEND column.

Can we get the Maximun value for above mentioned query?

Thanks

Creator
Author

Happy New Year!

Anyone can help my concern,

I have MONTHEND column contains below details

SEP-2019, OCT-2019, NOV-2019, DEC-2019

How can I get/filter date on a condition in edit expression(measures)

Data:

 ACCOUNT STATUS STAGE MONTHEND OUTSTANDING_BALANCE ACCOUNT1 A REGULAR NOV-2019 32,000 ACCOUNT2 A REPO DEC-2019 10,000 ACCOUNT3 A REGULAR NOV-2019 20,000 ACCOUNT4 C REGULAR NOV-2019 5,000

:sample query:

SUM({<[STATUS] = 'A', [STAGE] =   'REGULAR', [MONTHEND] = 'NOV-2019'>}OUTSTANDING_BALANCE)

Above query will only get the sum of Outstanding_Balance with all A status, Regular Stage and NOV-2019 monthend only.

The output will be : 52,000

Thank you

Community Browser