"IF Statement" - Returning ELSE output for blank intersections

Post Reply
Mark
Posts: 13
Joined: Mon Jun 18, 2018 2:38 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2017

"IF Statement" - Returning ELSE output for blank intersections

Post by Mark » Thu Jun 21, 2018 12:56 am

Hi all,

I have another query concerning a technique used in EP that I can not seem to replicate in TM1.

In EP, we've used a technique of flagging certain sets of data that allow for subsequent calculations based on these flags, or Lookup D-links.

An example of this is as follows.
- I have a calculation dimension with 2 elements.
- The data against this dimension is as follows:
- The first element has values against it of which some may intersections may not have data
- The other element is an expression. IF ('Major Project Number' <> "") THEN ("Major") ELSE ("Recurring")

In this example if I put only the IF and the THEN portion of my expression it validates successfully. However, as I want all remaining intersections to say "Recurring" adding the ELSE creates the following.

Using TM1 Performance Modeler the consistent calculation error which is explanatory enough is:
Feeders cannot be generated for this expression'
- This calculation expression cannot be fed.
- Feeders cannot be generated for this expression because the expression would return a non-zero value even if all the cubes in the model contained only zero values.
- The values of this expression will not consolidate correctly, and applying zero suppression may give incorrect results.
However, this is exactly what im trying to achieve, if something is blank/null then my ELSE portion of the logic should default as some other value.

How do we achieve this technique in TM1? It does seem relatively simple but ive come unstuck.

Thanks in advance for any help.
Mark.

MGrain
Posts: 9
Joined: Wed Nov 15, 2017 11:36 am
OLAP Product: TM1
Version: Various
Excel Version: Various

Re: "IF Statement" - Returning ELSE output for blank intersections

Post by MGrain » Thu Jun 21, 2018 9:00 am

Hi Mark

To evaluate a rule based on a NULL/empty cell is counter-intuitive in TM1, the rules engine with skipcheck and feeders is all about hypersparsity.

As usual with TM1 there are several different approaches you can take to solve the problem:

1) There's no need to put any rules in the lookup cube at all. Write the IF test in your subsequent rules you can just DB() back to your lookup cube to determine if you need to use "Major" or "Recurring".

2) Don't use Skipcheck/Feeders in your lookup cube, just turn them off. You don't *have* to use skipcheck and feeders in every cube. Lookup cubes tend to be dense anyway, it depends how many NULLS/Recurring you have, in a 2D cube it's probably not going to make any significant difference.

3) Populate the lookup cube via TI/use an attribute.

Your mileage may vary but personally speaking I would tend towards the 1st option. However, if you are going to need "Major" and "Recurring" in other places (websheets etc.) you may find the 2nd or 3rd options more useful.

Hope that helps
Mike

Mark
Posts: 13
Joined: Mon Jun 18, 2018 2:38 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2017

Re: "IF Statement" - Returning ELSE output for blank intersections

Post by Mark » Thu Jun 21, 2018 10:06 am

Hi Mike

Thanks for the well thought out response and detailed examples.

This gives me a fantastic amount of information to begin trying some of the techniques and also helps me understand good practice.

As it happens I think I managed to implement option 3 or something similarish.

I havnt populated my cube yet but all my formulas validated correctly which was a start.

I landed up creating two more elements on the calc dimension and intend to populate these with values from my TI ODBC import from the database. Dummy data if you will eg Y/N/1/0 etc.

Once I had the THEN and ELSE elements created I referenced them in the element that contained the IF logic.

Voila! Validation. Now assuming my import of data works I’m sure there are more than few seasoned TM1ers out there completely horrified at my solution :)

I also learnt the importance of specifying the type of data, text/numeric etc as I was just leaving things as general that was also causing me some issues.

Mark.

Post Reply