Highlighted
Frequent Contributor

Ignoring sorting in hierarchy when using "Last non-blank"

Hi,

I have an issue when it comes to summarizing in a module, and using Last-non blank.

I have two lists, L1 (units) and L2 (transactions for the parent unit). I then have a module where I have L2 as dimension and a number of line items. 

All transactions contains a valid from date, which I sort by in the module (only sorting on L2 level). Then, for all line items, I have set the summary as "Last non-blank" and hence want the L1 to show the values from the latest added transaction, if that value isn't blank. If it is, then it should look at the transaction with the second most recent valid from date and so on.
This used to work perfectly fine, but it seems that without me changing anything, the module ignores my sorting, and simply take the last non-blank based on transaction ID.

How it is (wrong)

LevelIDValid From (sorting)Line item 1Line item 2
L2#12019-01-01A1
L2#32019-01-02B2
L2#22019-01-03C3
L1Unit12019-01-02B2

How it should be

LevelIDValid From (sorting)Line item 1Line item 2
L2#12019-01-01A1
L2#32019-01-02B2
L2#22019-01-03C3
L1Unit12019-01-03C3

Since this used to work, and I haven't changed anything, I don't really know how I would go about to solved this. It seems like the module simply ignores my sorting on "Valid From"...

Any help is highly appreciated!

8 REPLIES 8
Highlighted
Master Anaplanner/Community Boss

Re: Ignoring sorting in hierarchy when using "Last non-blank"

Hi - 

 

Have you tried using "Max" for the summary settings instead?  That should work to give you the max date of a range of values.  I've used before for similar purposes.  Let me know if that works - screenshot below:  

 

range test.png

Highlighted
Certified Master Anaplanner

Re: Ignoring sorting in hierarchy when using "Last non-blank"

Hi @fredrickstraube ,

 

have also a look at this example using the Formula Summary and extra line items (but maybe it over complicates things): in this case your "Line Item 1" and "Line Item 2" are my "Line" and "Job Final" Line Items. Then I applied the sorting on the Rank Line ItemSort Max Date Grid.JPG

 

Sort Max Date Blueprint.JPG

 

Regards,

Antonio

 

Highlighted
Certified Master Anaplanner

Re: Ignoring sorting in hierarchy when using "Last non-blank"

You can avoid the SELECT that I put to simplify the example with a LOOKUP on another Line Item that results in the Parent of the List Items.

Highlighted
Certified Master Anaplanner

Re: Ignoring sorting in hierarchy when using "Last non-blank"

Nice solition.

Even more if 'Job' line item is a line taking value from other module item it's not nessecary to create it at all as extra line Item. You can take values for Job Max and Job Final straitly from original module.

Highlighted
Frequent Contributor

Re: Ignoring sorting in hierarchy when using "Last non-blank"

Hi everyone,

Thanks a lot for your input, it is highly appreciated! 

However, I am not sure it would work in my case. First, I have about 20 line items in my actual module, which mean I would have to add another "FINAL" for all of them -> 20 new lines items... I would really like to avoid that! Also, an L2 transaction will most likely only contain one of all my 20 line items - wouldn't that cause an issue?

Looking forward to hearing from you!

/Fredrick 

Highlighted
Community Boss

Re: Ignoring sorting in hierarchy when using "Last non-blank"

Hi,

 

To be clear, the sorting you do in the view, does not have any impact on any of the calculation / formula / summary.

Last Non Blank summary for example will result in the last item of the list, in the real order of that list (that you can see in list page), no matter what you SEE in your module (unless you have used subset).

 

In your case, it seems you want the values of the most recent transaction. You could use last non blank if your transactions were ordered. Anaplan will create them in the order that they are in your source, so if they are ordered there, they will be right in Anaplan. If that works for you then we can stop.

There is currently no automatic way to re-order list items automatically other than deleting and recreating in the right order, which is quite heavy.

 

Otherwise, as mentioned here, you could, and should, use the Max summary to know what is the greatest and thus most recent date. But that's going to work on the date only, not on the other items. Unfortunately I don't think there's a way to bring the values of the "last" transaction onto each line item in this view. You'll have to create additional line items as mentioned by jnoone.

 

One thing you can do, depending your usage of these values, is creating a module dedicated to showing you the latest values for each line item. First you need to know what's the latest transaction by doing like below:

   IF Date = Date[SELECT: Loading Lines.All Lines] THEN ITEM(Loading Lines) ELSE BLANK

With summary as last non blank

last transaction.PNG

 

then you can create a module with all your line items, without dimensions, like this:

see on the first line how I pull the last transaction and then use it as lookup to bring its values.

last transaction summary module blue prit.PNG

The result looks like this:

last transaction summary module.PNG

That gives you the most flexiblity in your formulas and what should be the latest value and also doesn't double up the size of your module by all transactions the way creating the "final" line items would.

Nathan Rudman
Anaplan Model Builder
Highlighted
New Contributor

Re: Ignoring sorting in hierarchy when using "Last non-blank"

Hi @nathan_rudman 

 

Thanks for the solution which does the job for a list with only one top-level item. 

 

Lets say that the scenario is that I have multiple top-level items:

clipboard_image_2.png

Is it possible to adjust your solution to address that scenario? 

 

In the scenario above i'd like to retrieve L2 items #9 for Unit 2 and #4 for Unit 1 and present them something like:

clipboard_image_1.png

 

Please tell if something needs clarification. 

 

Thanks in advance!

BR, Adam 

 

Highlighted
Community Boss

Re: Ignoring sorting in hierarchy when using "Last non-blank"

Hi,

 

you can dimension the result module by Unit 1/Unit2

Nathan Rudman
Anaplan Model Builder