Does anyone know if it's possible to do a firstoccurrence formula on a module with 2 lists in the 'applies to'?
You could create a line item with a concatenation of ITEM(Dimension 2) and make the First Occurrence in this Line Item using the Dimension 1, for example:
Is something like that you are looking for?
Can you give us a little more information, maybe a picture of some mocked up data. For instance, what are you trying to find the first occurrence of?
Thanks Leonardo and Rob for your responses. I'm not sure that the answer given achieves the result that I'm looking for. Please see screenshots below as an example. The first screenshot shows an example of what I'm trying to do. If you have 2 lists in the applies to (for example cost center and GL account), I'm trying to do a FirstOccurrence on the concatenation of the codes of those list items (see the "P&L/CC Combo Text" line item). Note that below, I've included "time" in the module, so the result should show a check box under the first item shown, and not the 3 items thereafter (since the concatenated item was already shown). I've tried using just 1 of the lists in the end of the isfirstoccurrence formula, but that doesn't seem to work (see columns 2 and 3). Hope this makes sense. Please let me know if you need more info.
Could you provide the business context as to "what" the purpose is for the ISFIRSTOCCURRENCE. Why do you need it? What is the significance of the time period?
The example below was just an example; not the exact use-case I'm looking for. I'm looking for a smooth way to create a list of the concatenation of 2 line items in a module that has 2 different lists in the applies-to. For example, if you had a module with an Acct List and a Cost Center List, and then you have an "Amount" column. If you were trying to create a saved view that just isolated the code of the Acct List with the code of the Cost Center list for items with Amount <> 0, and then publish those concatenated items to its own list. That's effectively what I'm trying to do.
The firstoccurrence function works only on 1 list.
So, from a technical point of view I can see only the solution of building a new list as a concatenation of the "Acct List" and a "Cost Center List" based on the saved view with the filter Amount <> 0 in the initial module.
Create a new module based on the new combination list, create a line item Amount which takes the data refering the initial module and there you will be able to calculate the first occurrence.
In the initial module, based on the concatenation of "Acct List" and a "Cost Center List" you can refer and see the first occurrence calculated in the combination module.
I also am unsure how can be applied in a real business case... 🙂
Hope it helps
I've actually had a couple of instances where I needed to employ this method.
1. If you're dealing with the text formatted line item, then creating a combination list (list 1 and list 2) like Alex mentioned is the best overall solution.
2. If you're dealing with a number formatted line item, or *list formatted line item, you can also use rank. *For the list formatted line item, the code has to be numeric or it has to be a numbered list or else, rank won't work.* This method is a bit klugey and requires a pre-built list for numbers but if you want to think outside of the box, I recommend trying it and see how this plays out.
3. Bonus point. I have been toying around with combining list 1 IFO and list 2 IFO and trying to find creative ways with combining these two using AND / OR and other summary methods. I haven't been able to find a workaround yet but if you do come across a solution, please do share with the community.