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)

Level	ID	Valid From (sorting)	Line item 1	Line item 2
L2	#1	2019-01-01	A	1
L2	#3	2019-01-02	B	2
L2	#2	2019-01-03	C	3
L1	Unit1	2019-01-02	B	2

How it should be

Level	ID	Valid From (sorting)	Line item 1	Line item 2
L2	#1	2019-01-01	A	1
L2	#3	2019-01-02	B	2
L2	#2	2019-01-03	C	3
L1	Unit1	2019-01-03	C	3

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!

Accepted answers

All comments

jnoone

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

a.dilieto

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 Item Sort Max Date Grid.JPG

Sort Max Date Blueprint.JPG

Regards,

Antonio

a.dilieto

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.

dmitrii.mamaev

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.

fredrickstraube

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

nathan_rudman

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.

adlarsson

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:

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:

Please tell if something needs clarification.

Thanks in advance!

BR, Adam

nathan_rudman

Hi,

you can dimension the result module by Unit 1/Unit2

Quick Links

Can you help?

Unanswered questions

OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385