Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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") |
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.
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
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
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),'|')&')'))
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