topic Re: select list box selections based on another list box selection in QlikView

select list box selections based on another list box selection

robmarr789 — Sat, 16 Nov 2024 19:41:38 GMT

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)

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

QlikJunkie — Tue, 12 Nov 2019 13:13:42 GMT

Try This.....!!

NO_OF_MONTH	LAST_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")

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

robmarr789 — Tue, 12 Nov 2019 13:39:38 GMT

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.

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

Arthur_Fong — Tue, 12 Nov 2019 13:53:10 GMT

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

Refer qvw attached as reference.

Thanks and regards,

Arthur Fong

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

robmarr789 — Tue, 12 Nov 2019 17:39:34 GMT

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

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

Arthur_Fong — Wed, 13 Nov 2019 00:00:43 GMT

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

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

Brett_Bleess — Mon, 18 Nov 2019 14:07:51 GMT

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