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: 
pinky1234
Contributor III
Contributor III

Dynamic Last 6 months in List box

Hi,

I have month year field like this Jan-2021,Feb-2021,Mar-2021,Apr-2021...etc. So I wanted to show dynamically last 6 months in list box. For ex: On Aug 1st 2021 my list box should show  Feb-2021,Mar-2021,Apr-2021,May-2021,June-2021,July-2021. Can some one help me on this? Thanks!

4 Replies
Kushal_Chawda

@pinky1234  if your month field is in Numeric format, you can try below in listbox expression. Otherwise, you first need to convert your month field in Numeric format using Date#

= aggr(only({<Month = {">=$(=date(addmonths(max(Month),-5),'MMM-YYYY'))<=$(=date(max(Month),'MMM-YYYY'))"}>}Month),Month)

pinky1234
Contributor III
Contributor III
Author

It is not working for me. I have field like this MonthYear and format is Jan-2021,Feb-2021,Mar-2021..etc. 

I am trying to use like this formula: = aggr(only({<MonthYear = {">=$(=date(addmonths(max(MonthYear),-5),'MMM-YYYY'))<=$(=date(max(MonthYear),'MMM-YYYY'))"}>}MonthYear),MonthYear) But resulting Aug-2022,Sep-2022,Oct-2022,Nov-2022,Dec-2022,Jan-2023. But  i need it last 5 months in 2021.

pinky1234
Contributor III
Contributor III
Author

Last 5 months in 2021 means from Feb-2021 to July-2021. 

Kushal_Chawda

@pinky1234  try below

aggr(only({<MonthYear = {">=$(=date(addmonths(today(1),-5),'MMM-YYYY'))<=$(=date(today(1),'MMM-YYYY'))"}>}MonthYear),MonthYear)