Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
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

13 Replies
DavidM
Partner
Partner

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)

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)
DavidM
Partner
Partner

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

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
Support (Former)
Support (Former)

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.
kristeljoymalapitan
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

kristeljoymalapitan
Author

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
Author

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
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:

ACCOUNTSTATUSSTAGEMONTHENDOUTSTANDING_BALANCE
ACCOUNT1AREGULARNOV-201932,000
ACCOUNT2AREPODEC-201910,000
ACCOUNT3AREGULARNOV-201920,000
ACCOUNT4CREGULARNOV-20195,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