Populating Numbered List from a Module with 4 or more dimensions:

BhumikaTewari
Occasional Contributor

Populating Numbered List from a Module with 4 or more dimensions:

Use Case: I am trying to create a Numbered List from a module with 4 dimensions typically SKU, Tier, Product and Level  & few Line items. For modules dimensioned by 3 lists it is straightforward to create a View as the source of the Import with the 3 lists as Column items and the Line Items as Row items. Unfortunately this does not extend to 4 lists as you can't have 4 lists as Column items in the view pivot.

 

Source Module:

CommunityMember113675_0-1637069769023.png

 

Target List:

CommunityMember113675_1-1637069798621.png

 

 

Solution-

Step1:

Create a Helper list with 10k items(can be extended depending on your combination size).

CommunityMember113675_2-1637069817412.png

 

Step2:

Create 2 lineitems in your source Module:

Name

Format

Formula

Ranking

Number

RANK(1, ASCENDING, SEQUENTIAL)

Helper Format

Helper(list)

FINDITEM(Helper, TEXT(Ranking))

CommunityMember113675_3-1637069830334.png

 

Step3:

Create a new Staging Module with Helper list as the dimension:

Create a lineitem to fetch the Concat from the Source Module & another for filtering the saved view:

Name

Format

Formula

Concat

Text

Source Module.Concat[FIRSTNONBLANK: Source Module.Helper Format]

NOTBLANK Concat

Boolean

ISNOTBLANK(Concat)

CommunityMember113675_4-1637069839039.png

CommunityMember113675_9-1637070075094.png

 

Step4:

Create a saved view in the above Staging module.

CommunityMember113675_6-1637069874225.png

CommunityMember113675_7-1637069882463.png

 

Step5:

Create an action to transfer this data from the above Saved view into the numbered list.

CommunityMember113675_8-1637069892705.png

 

 

 

 

5 REPLIES 5
JaredDolich
Moderator

@BhumikaTewari 

Nice solution!


Jared Dolich
BhumikaTewari
Occasional Contributor

Thank you @JaredDolich
PhilippErkinger
Certified Master Anaplanner

Very smart idea, love it! I would recommend to add a counter in a separate module that checks if the 10k list items is enough. Just to avoid hidden failures. 🙂

Planning Rockstar at Bluesprint
BhumikaTewari
Occasional Contributor

Thank you @PhilippErkinger.
Couldn't agree more on the Counter module & it can even be used to populate a subset of the bigger(Helper) list.
PhilippErkinger
Certified Master Anaplanner

...and not to forget; RANK has a limit of 50 Mio Cells. But at such a high cell count, FINDITEM will also become concerning for the performance. 😉 

Planning Rockstar at Bluesprint