Using nested if statements in the script or a chart

[#1]

I'm struggling putting a nested 'if statement' in a specific chart where i want to show Age, i am using it as a dimension and my formula is as follows:

=

IF((Today()-DateOfBirth)=<20,'0-20',

IF

((Today()-DateOfBirth)=<30,'21-30',

IF

((Today()-DateOfBirth)=<40,'31-40',

IF

((Today()-DateOfBirth)=<50,'41-50',

IF

((Today()-DateOfBirth)=<60,'51-60',

IF

((Today()-DateOfBirth)=<70,'61-70','Above 70')))))))

I'd really apprecate it if someone could point out what i'm doing wrong. I think you can see what i'm trying to do. Tried to put this formula in the script aswell. Any suggestions??

[biester · Accepted solution]

Hi,

Use IF very carefully and avoid nested IFs like the plague ;-)). I'd say that is a prime example for intervalmatch, and you shouldn't need a single "if" here! Have you already tried with intervalmatch?

Rgds,
Joachim

[#3]

Hi

Thank you very much, that is the answer, works out brilliantly.. Cheers

[#4]

biester wrote:
Hi,
Use IF very carefully and avoid nested IFs like the plague ;-)). I'd say that is a prime example for intervalmatch, and you shouldn't need a single "if" here! Have you already tried with intervalmatch?
Rgds,
Joachim<div></div>

... on the other hand: when working with mass-data intervalmatch really slows down your performance while the if-statement (even if it is tricky to write and maintenance) does not ...

Best regards

Stefan

[johnw]

I think the original problem was that today() and DateOfBirth are both dates, so when you subtract, you're getting the number of days, not the number of years. I would suggest the age() function, i.e., age(today(),DateOfBirth). While a nested IF statement can be slow in a case like this where you're likely forcing it to continually recalculate the age, I certainly don't avoid them like the plague. I actually use them quite often simply because it is so clear what's going on, even to an untrained observer.

if(age(today(),DateOfBirth)<=20, '0-20'
,if(age(today(),DateOfBirth)<=30,'21-30'
,if(age(today(),DateOfBirth)<=40,'31-40'
,if(age(today(),DateOfBirth)<=50,'41-50'
,if(age(today(),DateOfBirth)<=60,'51-60'
,if(age(today(),DateOfBirth)<=70,'61-70'
,'Above 70'))))))

There are other approaches that might be shorter and faster, if less clear, such as the below. But unless I have an overriding reason, I'd go with what's clear.

if(age(today(),DateOfBirth)>70,'Above 70',pick(2+floor((age(today(),DateOfBirth)-1)/10),'0-20','0-20','0-20','21-30','31-40','41-50','51-60','61-70'))

Edit: These are basically chart expressions that you COULD use in the script. If you want to do it in the script, which is probably a good idea, then something like an intervalmatch would certainly be a good solution. It would be hard for me to say without testing whether the IFs or an intervalmatch would be faster in this particular case. In any case, if you have it working with intervalmatch, I'd just stick with that.

[johnw]

Stefan WALTHER wrote:... on the other hand: when working with mass-data intervalmatch really slows down your performance while the if-statement (even if it is tricky to write and maintenance) does not ...

I've actually used intervalmatch to speed up some scripts, so I thought I'd do a little scripting comparison. First, I load a 5 million row table with an ID and an Age between 0 and 80. Then, for the IF, I do a left join of the IF expression to get the age range. For the intervalmatch, I do a much more complicated process of building the interval table, intervalmatching and left joining the raw data to the interval table, left joining the interval table back to the raw data, and finally dropping the interval table. Timing from when the raw data has finished loading to the completion of the script:

• The IF version ran in 34 seconds
• The intervalmatch version ran in 14 seconds

I did notice that that the intervalmatch version used more MEMORY, but it executed much more quickly. So it's a trade off - simplicity vs. speed vs. memory.

All tests performed in version 9. Relative performance subject to change in new versions. Example data isn't representative of real data. Your mileage may vary. Any other necessary disclaimers.