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

[lorenzoconforti]

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)

See also attached application

[kristeljoymalapitan]

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

[lorenzoconforti]

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

[kristeljoymalapitan]

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.