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

[kristeljoymalapitan]

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

[DavidM]

Like this

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

[Anil_Babu_Samineni]

@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)

[DavidM]

@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

[MindaugasBacius]

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)

[Brett_Bleess]

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

[kristeljoymalapitan]

Hi,

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

I will mark solve once mentioned formula works.

Thank you

[kristeljoymalapitan]

Hi @Anil_Babu_Samineni 

Suggested does not work.

I user below query to get MONTHEND column.

UPPER(DATE(Addmonths(Today(),-1),'MMM-YYYY') AS MONTHEND 

Can we get the Max value for above mentioned query?

Thanks

[kristeljoymalapitan]

Hi @DavidM 

Suggested does not work.

I used below query to get MONTHEND column.

UPPER(DATE(Addmonths(Today(),-1),'MMM-YYYY') AS MONTHEND

Can we get the Maximun value for above mentioned query?

Thanks

[kristeljoymalapitan]

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