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!
Answers
-
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:
1 -
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
Regards,
Antonio
1 -
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.
1 -
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.
0 -
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
0 -
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
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.
The result looks like this:
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.
2 -
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
0 -
Hi,
you can dimension the result module by Unit 1/Unit2
0
