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

select list box selections based on another list box selection

On the attached I am trying to get it so that when L12 is selected in one list box it will select the last full 12 months from the yearmonth list box. Likewise if L3 is selected is will select the last full 3 months. The selection will automatically change as we move into the subsequent months.

For example today:

L12 - will select periods 201910-201811 (12 selections)

L3 - will select periods 201910-201908 (3 selections)

Labels (4)
6 Replies
QlikJunkie
Contributor
Contributor

Try This.....!!

NO_OF_MONTHLAST_CLOSING_PERIOD
L1("Jan")
L2("Jan"|"Feb")
L3("Jan"|"Feb"|"Mar")
L4("Jan"|"Feb"|"Mar"|"Apr")
L5("Jan"|"Feb"|"Mar"|"Apr"|"May")
L6("Jan"|"Feb"|"Mar"|"Apr"|"May"|"Jun")
L7("Jan"|"Feb"|"Mar"|"Apr"|"May"|"Jun"|"Jul")
L8("Jan"|"Feb"|"Mar"|"Apr"|"May"|"Jun"|"Jul"|"Aug")
L9("Jan"|"Feb"|"Mar"|"Apr"|"May"|"Jun"|"Jul"|"Aug"|"Sep")
L10("Jan"|"Feb"|"Mar"|"Apr"|"May"|"Jun"|"Jul"|"Aug"|"Sep"|"Oct")
L11("Jan"|"Feb"|"Mar"|"Apr"|"May"|"Jun"|"Jul"|"Aug"|"Sep"|"Oct"|"Nov")
L12("Jan"|"Feb"|"Mar"|"Apr"|"May"|"Jun"|"Jul"|"Aug"|"Sep"|"Oct"|"Nov"|"Dec")
robmarr789
Contributor
Contributor
Author

Thanks but unfortunately this doesn't work.

 

L1 for example should select period 201910 as this is the last full month from today.  (Next month is would select 201911).

L2 would select 201910 and 201909 and so on....

It also needs to select the periods in the existing format in the list box.

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Try this:

In Settings > Document Properties > Triggers > Field Event Triggers > SelectionPeriod > OnSelect > Edit Actions > Select In Field > Field : YearMonth > Search String >

=if(GetFieldSelections(SelectionPeriod)='L12','('&Concat(DISTINCT if(aggr(Rank(YearMonth),YearMonth)<=12, YearMonth),'|')&')',
if(GetFieldSelections(SelectionPeriod)='L3','('&Concat(DISTINCT if(aggr(Rank(YearMonth),YearMonth)<=3, YearMonth),'|')&')'))

 

MC.PNG

Refer qvw attached as reference.

Thanks and regards,

Arthur Fong

robmarr789
Contributor
Contributor
Author

Sorrt I don't think I've explained correctly.

 

When I say last 3 months I mean the most recent full 3 months from today i.e. 

 

today is 12/11/19 

Period selection would be 201910,201909,201908

The following month the L3 selction would return periods 201911,201910,201909

 

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Try this:

=if(GetFieldSelections(SelectionPeriod)='L12','('&Concat(DISTINCT if(aggr(Rank(YearMonth),YearMonth)<=14 and YearMonth<num#(text(date(today(),'YYYYMM'))), YearMonth),'|')&')',
if(GetFieldSelections(SelectionPeriod)='L3','('&Concat(DISTINCT if(aggr(Rank(YearMonth),YearMonth)<=5 and YearMonth<num#(text(date(today(),'YYYYMM'))), YearMonth),'|')&')'))

Brett_Bleess
Former Employee
Former Employee

Rob, did Arthur's latest post get you a working solution?  If so, do not forget to return to the thread and use the Accept as Solution button on the post(s) that helped you get the working solution, so the posters receive credit for the assistance provided and other Community members know what actually worked.  If you are still working upon things, please leave an update with current status.

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.