Workaround for a Last value function
Hi, There is no Last Value function in Anaplan (Adaytum had one BiF @Last), thus I have tried to modelize something because I was needied this Last Value feature for a customer of mine. The business need is as follows : the user enter values in some month, not all of them, of course months are random, and the following month must use the last entered value, until replaced by the new entered value. I have tried several combination of POST, PREVIOUS, CUMULATE, ... and lot of other stuff but finally I came back to the proposed solution. Although it works pretty well and give the expected result (within one year in the current state of it, not across several years), I do not like it because it is heavy, ****, and non estetical. I would glady look at a better and more elegant way to adress this Last value Issue. Thanks in advance for any proposal. KR Michel. I have attached 2 printscreens and an Excel file with the module blueprint. Here are the formulas. It works for 5 values changes within the year, but of course you can extend this.
| Enter value | |
| Value change = 1 | IF Enter value <> 0 AND Enter value <> CUMULATE(Enter value) THEN 1 ELSE 0 |
| Cumulate Changes | CUMULATE('Value change = 1') |
| 1st Value | IF 'Value change = 1' = 0 THEN Enter value ELSE 0 |
| 2nd Value | IF Cumulate Changes = 1 AND Enter value <> 0 THEN Enter value ELSE 0 |
| 3rd Value | IF Cumulate Changes = 2 AND Enter value <> 0 THEN Enter value ELSE 0 |
| 4th Value | IF Cumulate Changes = 3 AND Enter value <> 0 THEN Enter value ELSE 0 |
| 5th Value | IF Cumulate Changes = 4 AND Enter value <> 0 THEN Enter value ELSE 0 |
| Last value 1 | IF CUMULATE(Enter value) <> 0 AND Cumulate Changes = 0 THEN YEARVALUE('1st Value') ELSE 0 |
| Last value 2 | IF Cumulate Changes = 1 THEN YEARVALUE('2nd Value') ELSE 0 |
| Last value 3 | IF Cumulate Changes = 2 THEN YEARVALUE('3rd Value') ELSE 0 |
| Last value 4 | IF Cumulate Changes = 3 THEN YEARVALUE('4th Value') ELSE 0 |
| Last value 5 | IF Cumulate Changes = 4 THEN YEARVALUE('5th Value') ELSE 0 |
| Last Value | 'Last value 1' + 'Last value 2' + 'Last value 3' + 'Last value 4' + 'Last value 5' |
Be careful, there are some SUmmary options required to achieve the proper result.
Best Answer
-
Hi Michel
Interesting case
Please see attached my solution
I used 5 lineitems to better describe the logic (hope it could be solved with 3 lineitems)0
Answers
-
Hi Oleg,
congrats and many thanks for this far better solution : 5 lines items versus 15 in my solution, no comments !
In addition to bringing a far easier and better solution, you also make me ware that it is possible to use a formula on a ine tem which include itself as a result without creating a loop :
Previous Data = IF Input = 0 THEN PREVIOUS(Previous Data) ELSE Cumulate Values - PREVIOUS(Cumulate Changes)
I was afraid of creating a loop and I did not dare neither even try this approach. I keep it in mind for future usage.
Many thanks again.
KR
Michel.
1