Retrieving last date with value
Hi,
I have a module where I have time as dimension, as well as a list of equipment. The module is on daily level and has a value of either 1 or 0 depending on if the equipment is in service or not. These values are imported from another Anaplan model.
I then have a second module, where I have the same list of equipment as dimension. In this module I have 2 line items - "In Service Date" and "Out of service date". What I am trying to do, and cannot figure out how to solve, is for the "In service date" show the first date with a 1 in it, indicating availability, and for the "out of service date", show the last date with availability.
Module 1:
| Equipment | 01/01-19 | 02/01-19 | 03/01-19 | 04/01-19 | 05/01-19 | 06/01-19 | 07/01-19 | 08/01-19 | 09/01-19 |
| #1 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 0 | 0 |
| #2 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
Module 2:
| Equipment | In service date | Out of service date |
| #1 | 03/01-19 | 07/01-19 |
| #2 | 01/01-19 | 04/01-19 |
Does anyone know what this formula would look like? Note that it is not possible for an equipment to be in service, then out of service, and then in service again - once it is out of service, it is out of service for good.
Any help is highly appreciated!
Best Answer
-
I have a couple of solutions for you. First, create a SYS Global module where you can put in get the current period which is set in Time or it can be a manual update by the admin. I have it as a formula:
I have my "transactional" module setup like you, just a few more inputs:
I have added two line items with the following formulas:
First In Service: IF Data > 0 THEN START() ELSE BLANK
Out of Service: IF Data > 0 THEN START() ELSE BLANK
The key difference is the summaries where I set them to First non-blank and Last non-blank.
In the results module, I have the following formulas in case you wanted the Start and Stop over other periods (All Time, Current FY Year, and Current Month):
In Service Date All Time: Equipment.First In Service[SELECT: TIME.All Periods]
Out of Service Date All Time: Equipment.Out of Service[SELECT: TIME.All Periods]
In Service Current Year: Equipment.First In Service[LOOKUP: SYS Global.Current Year]
Out of Service Current Year: Equipment.Out of Service[LOOKUP: SYS Global.Current Year]
In Service Month: Equipment.First In Service[LOOKUP: SYS Global.Current Month]
Out of Service Month: Equipment.Out of Service[LOOKUP: SYS Global.Current Month]
For a result of:
Hope this helps,
Rob
0
Answers
-
Hi Rob,
This worked perfectly, thanks a lot!
/Fredrick
0