Nested conditional IF statements determining age ranges

[Taavu82]

Hello Qlik Community!

I'm looking to use a condition of an Issue Status (Closed/Open) to then calculate an age range. I have a nested IF statement. The first condition is stating if the issue is "Closed" to calculate the age range between the Issue Created Date, and Issue Closed Date. 

However, the second condition, if the issue is "Open", I would like to calculate the age range using the difference between the Issue Created Date, and today. 

I can get these to work independently, but not together. Can someone let me know what I may be missing? Here is what I'm using: 

=IF(ISSUE_STATUS_NAME='Closed',
IF(ISSUE_CLOSED_DATE2 - ISSUE_CREATED_DATE2 <= '0', '0 Days',
IF(ISSUE_CLOSED_DATE2 - ISSUE_CREATED_DATE2 <= '1', '1 Day',
IF(ISSUE_CLOSED_DATE2 - ISSUE_CREATED_DATE2 <= '2', '2 Days',
IF(ISSUE_CLOSED_DATE2 - ISSUE_CREATED_DATE2 <= '5', '3-5 Days',
IF(ISSUE_CLOSED_DATE2 - ISSUE_CREATED_DATE2 <= '7', '6-7 Days',
IF(ISSUE_CLOSED_DATE2 - ISSUE_CREATED_DATE2 <= '10', '8-10 Days',
IF(ISSUE_CLOSED_DATE2 - ISSUE_CREATED_DATE2 <= '14', '11-14 Days',
IF(ISSUE_CLOSED_DATE2 - ISSUE_CREATED_DATE2 <= '30', '15-30 Days',
IF(ISSUE_CLOSED_DATE2 - ISSUE_CREATED_DATE2 <= '60', '31-60 Days',
IF(ISSUE_CLOSED_DATE2 - ISSUE_CREATED_DATE2 <= '90', '61-90 Days',
IF(ISSUE_CLOSED_DATE2 - ISSUE_CREATED_DATE2 >= '91', '91+ Days',
IF(ISSUE_STATUS_NAME='Open',
IF(Today() - ISSUE_CREATED_DATE2 <= '0', '0 Days',
IF(Today() - ISSUE_CREATED_DATE2 <= '1', '1 Day',
IF(Today() - ISSUE_CREATED_DATE2 <= '2', '2 Days',
IF(Today() - ISSUE_CREATED_DATE2 <= '5', '3-5 Days',
IF(Today() - ISSUE_CREATED_DATE2 <= '7', '6-7 Days',
IF(Today() - ISSUE_CREATED_DATE2 <= '10', '8-10 Days',
IF(Today() - ISSUE_CREATED_DATE2 <= '14', '11-14 Days',
IF(Today() - ISSUE_CREATED_DATE2 <= '30', '15-30 Days',
IF(Today() - ISSUE_CREATED_DATE2 <= '60', '31-60 Days',
IF(Today() - ISSUE_CREATED_DATE2 <= '90', '61-90 Days', '91+ Days')))))))))))))))))))))))

Thanks! 

[MarcoWedel]

ISSUE_STATUS_NAME='Open' can only be true in the "else" part of an If() function that checks for ISSUE_STATUS_NAME='Closed', right?

[Taavu82]

Correct! The Issue Status is either/or, but never both at the same time. I just don't know how to write the statement it so it's looking at it like an "else". 

[marcus_sommer]

I think I would do the following:

applymap('m',
   pick(
      match(ISSUE_STATUS_NAME, 'Closed', 'Open'), 
       ISSUE_CLOSED_DATE2, Today()) - ISSUE_CREATED_DATE2, 91) as Cluster

and within the mapping table is just a listing from 0 - 91 + the appropriate Cluster values (it's probably just a minute copy & paste within Excel). 

[Taavu82]

Thanks for the input, Marcus. I'm not familiar with Clusters, so I'll have to research. Appreciate the feedback though! 

[vinieme12 · Accepted solution]

IF(IF(ISSUE_STATUS_NAME='Closed',ISSUE_CLOSED_DATE2,Today()) - ISSUE_CREATED_DATE2 <= '0', '0 Days',
IF(IF(ISSUE_STATUS_NAME='Closed',ISSUE_CLOSED_DATE2,Today()) - ISSUE_CREATED_DATE2 <= '1', '1 Day',
IF(IF(ISSUE_STATUS_NAME='Closed',ISSUE_CLOSED_DATE2,Today()) - ISSUE_CREATED_DATE2 <= '2', '2 Days',
IF(IF(ISSUE_STATUS_NAME='Closed',ISSUE_CLOSED_DATE2,Today()) - ISSUE_CREATED_DATE2 <= '5', '3-5 Days',
IF(IF(ISSUE_STATUS_NAME='Closed',ISSUE_CLOSED_DATE2,Today()) - ISSUE_CREATED_DATE2 <= '7', '6-7 Days',
IF(IF(ISSUE_STATUS_NAME='Closed',ISSUE_CLOSED_DATE2,Today()) - ISSUE_CREATED_DATE2 <= '10', '8-10 Days',
IF(IF(ISSUE_STATUS_NAME='Closed',ISSUE_CLOSED_DATE2,Today()) - ISSUE_CREATED_DATE2 <= '14', '11-14 Days',
IF(IF(ISSUE_STATUS_NAME='Closed',ISSUE_CLOSED_DATE2,Today()) - ISSUE_CREATED_DATE2 <= '30', '15-30 Days',
IF(IF(ISSUE_STATUS_NAME='Closed',ISSUE_CLOSED_DATE2,Today()) - ISSUE_CREATED_DATE2 <= '60', '31-60 Days',
IF(IF(ISSUE_STATUS_NAME='Closed',ISSUE_CLOSED_DATE2,Today()) - ISSUE_CREATED_DATE2 <= '90', '61-90 Days', '91+ Days'))))))))))

[Taavu82]

Thank you Vineeth Pujari, very helpful! 😊