FINDITEM formula

Hi all,

I have a list named "TAT Hours" and a line item "TOTAL HOURS" that is in number format.

I used FINDITEM formula to get the number of hours (TAT Hours) in the list, here is the formula:

FINDITEM(TAT Hours, TEXT(IF TOTAL HOURS <= VALUE(NAME(TAT Type.Min Hours[LOOKUP: 'TAT Type1'])) THEN VALUE(NAME(TAT Type.Min Hours[LOOKUP: 'TAT Type1'])) ELSE IF TOTAL HOURS > VALUE(NAME(TAT Type.Max Hours[LOOKUP: 'TAT Type1'])) THEN VALUE(NAME(TAT Type.Max Hours[LOOKUP: 'TAT Type1'])) ELSE TOTAL HOURS))

It gives me the correct result, which is the corresponding "TAT Hours", but I noticed that if I will input any number in line item TOTAL HOURS that ends in zero (0) it will not give any result, just blank. But if I will input any other number that is not ending with zero, it gives back the correct result.

I attached a screenshot, wherein the first line item gives the correct result but the next item does not (since it ends with zero)

FINDITEM with number ending in ZERO.PNG

Thank you,

Sarah

Accepted answers

DavidSmith

@SarahE

Silly question:

Any reason why you can't set the code for 0.10 to 0.1, 0.20 to 0.2 etc.?

That makes it easy!

David

DavidSmith

@SarahE

Alternatively, this might work

Check for the length of the "number" and create the text string

David

DavidSmith

@SarahE

Brilliant

The one thing I really stress to everyone is take that step back and look at the problem differently and laterally and logically All too often we go too deep too soon and miss the obvious. That is not a criticism, but I think modellers tend to think Anaplan must be complicated because of the nature of multi-dimensionality. Anaplan is designed to be simple and often the best solutions are the most obvious.

I've started quoting Tim Peters:

Simple is better than complex, complex is better than complicated.

David

All comments

DavidSmith

@SarahE

Firstly I would definitely split that formula up.

1. It will help you debug the issue!

2. It will be much more efficient

3. You have the same formula expressions repeated multiple time

VALUE(NAME(TAT Type.Min Hours[LOOKUP: 'TAT Type1']
VALUE(NAME(TAT Type.Max Hours[LOOKUP: 'TAT Type1']

I suspect the problem is that the value of 0.70 is resolving to 0.7. If the name or code code of the TAT Hours is 0.7 it will not find it. try changing the name and/code to 0.70

But definitely split up that formula!

David

SarahE

Hi, thank you @DavidSmith for the response. I also thought of that, that Anaplan might read it as 0.7 instead of 0.70 but the TAT Hours list looks like this (list name and code in 2 decimal places, with zero in the end):

TAT Hours.PNG

Also now, I tried to look up using number rounded up in 2 decimal place (used Round(Total Hours, 2) formula for it to match the decimal places in the TAT Hours list) but the same problem goes.

Hope you can help me with this, THANK YOU.

DavidSmith

@SarahE

Silly question:

Any reason why you can't set the code for 0.10 to 0.1, 0.20 to 0.2 etc.?

That makes it easy!

David

DavidSmith

@SarahE

Alternatively, this might work

Check for the length of the "number" and create the text string

David

SarahE

This silly question solved my query. I am thinking way too far than how simple the solution should be. It took one silly question for me to think 🙂 THANK YOU again for this @DavidSmith, my module now calculates all the hours I need 🙂

DavidSmith

@SarahE

Brilliant

I've started quoting Tim Peters:

Simple is better than complex, complex is better than complicated.

David

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…