Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kristeljoymalapitan

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)
13 Replies
lorenzoconforti
Specialist II
Specialist II

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
Author

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
Specialist II
Specialist II

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
Author

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.