Creating a subset by "reading" the index of a prompted value

[SimonWM]

Hi again,

loads of questions popping up recently... This time, I am struggeling with the following issue

A user can select items via a multi-select prompt. The list he is picking from is sorted:

A
B
C
D
E

Now, I need the list of items (MUN) up to the "highest" chosen item (2 examples: chosen (B, C, D) results in list (A, B, C, D) // chosen (A, D) -> list (A, B, C, D) )

I was hoping, one could "read" the index of the highest item (e.g. "D" is 4 therefore pick everything <= 4). I couldn't find the necessary functions though :(

What I managed so far, is retrieving the MUN from the "highest" item like this:
tail(set(#promptmany('Parameter','mun')#))

Again, I would be happy about any input.

Regards,
Simon

[CognosPaul]

The periodsToDate function might work here.

periodsToDate(,
level(parent(item(#promptmany('Parameter','mun')#,0)))
,item(tail(set(#promptmany('Parameter','mun')#)),0)
)

It's used mainly for ordered time dimensions, but it should still work here.

[SimonWM]

Thanks for your help so far, CognosPaul.

I couldn't get the function to work though. My example earlier is a simplification of the real Situation:

We've got a hierarchy like this

[...]
2016

• 2016 Q1
• 2016 Q2
• 2016 Q3
• 2016 Q4

2017

• 2017 Q1
• 2017 Q2
• 2017 Q3
• 2017 Q4

[...]

The user can chose from a flatened list like
2016 Q1
2016 Q2
2016 Q3
2016 Q4
2017 Q1
2017 Q2
2017 Q3
2017 Q4

Your example works within one year. Means, if I select "2016 Q2" and "2017 Q3" (e.g.), the resulting set is ("2017 Q1", "2017 Q2", "2017 Q3"), as the "latest" member is from within the 2017 node. All years are children of their shared root-member "Period".

As the first argument of the "level"-function requires a single member, I am struggeling to make it work with all years. My wanted result for the above example would be  ("2016 Q1", "2016 Q2", "2016 Q3", "2016 Q4", "2017 Q1", "2017 Q2", "2017 Q3").

Thanks again for your effort so far!

[CognosPaul]

That makes it a lot easier. Replace the level function from my example and put in the Period level. Read the tip for periodsToDate to understand the function and what's going on.