Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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)

6 Replies
Highlighted
Contributor
Contributor

Re: select list box selections based on another list box selection

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")
Highlighted
Contributor
Contributor

Re: select list box selections based on another list box selection

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.

Highlighted
Partner
Partner

Re: select list box selections based on another list box selection

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

Highlighted
Contributor
Contributor

Re: select list box selections based on another list box selection

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

 

Highlighted
Partner
Partner

Re: select list box selections based on another list box selection

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),'|')&')'))

Highlighted
Digital Support
Digital Support

Re: select list box selections based on another list box selection

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.