topic Re: how to automatically filter previous month and year in edit expression in qlik sense? in QlikView

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

kristeljoymalapitan — Sat, 16 Nov 2024 19:25:21 GMT

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

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

DavidM — Thu, 19 Dec 2019 08:28:41 GMT

Like this

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

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

Anil_Babu_Samineni — Thu, 19 Dec 2019 08:32:43 GMT

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

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

DavidM — Thu, 19 Dec 2019 09:47:22 GMT

@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

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

MindaugasBacius — Thu, 19 Dec 2019 10:00:03 GMT

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)

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

Brett_Bleess — Thu, 26 Dec 2019 13:28:05 GMT

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

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

kristeljoymalapitan — Fri, 27 Dec 2019 02:24:48 GMT

Hi,

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

I will mark solve once mentioned formula works.

Thank you

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

kristeljoymalapitan — Fri, 27 Dec 2019 02:30:45 GMT

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

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

kristeljoymalapitan — Fri, 27 Dec 2019 10:19:32 GMT

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

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

kristeljoymalapitan — Thu, 02 Jan 2020 05:43:52 GMT

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

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

lorenzoconforti — Thu, 02 Jan 2020 11:56:15 GMT

The issue you are having here is that your MONTHEND field is not a date field, it's just string

The first thing to do is to convert it to a date; see below, I've created a new new field MONTHEND2 which properly represents the month end:

Data:
load *,
monthend(date#(SubField(MONTHEND, '-', 1)&SubField(MONTHEND, '-', 2), 'MMMYY')) as MONTHEND2;
load * Inline [
ACCOUNT, STATUS, STAGE, MONTHEND, OUTSTANDING_BALANCE
ACCOUNT1, A, REGULAR, Nov-19, 32000
ACCOUNT2, A, REPO, Dec-19, 10000
ACCOUNT3, A, REGULAR, Nov-19, 20000
ACCOUNT4, C, REGULAR, Nov-19, 5000
];

Once you have that, your expression will work as expected (please note I'm looking at two months earlier in the below expression as now we are in Jan and your data is from December):

=SUM( {<STATUS = {"A"}, STAGE = {"REGULAR"}, MONTHEND2 = {"$(=MonthEnd(AddMonths(Today(),-2)))"}>} OUTSTANDING_BALANCE)

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

kristeljoymalapitan — Thu, 09 Jan 2020 02:28:29 GMT

Hi @lorenzoconforti ,

Unable to import given app due to encountered error, please see attached screenshots.

Please be informed that I am using Qlik Sense Version 12.44.1

Thank you

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

lorenzoconforti — Thu, 09 Jan 2020 09:51:27 GMT

Please see Qlik Sense application attached. Just place it in your "Documents\Qlik\Sense\Apps" folder and it will then show up in your Qlik Sense Desktop hub

Lorenzo

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

kristeljoymalapitan — Fri, 10 Jan 2020 06:36:50 GMT

How? Can you share sample screenshot, as per placing to you given path location, app is not available, please be informed that I am using Qlik Sense thru network (version 12.44.1) and not Qlik Sense Desktop.