I'm struggling with a formula in a straight table.
I have a dataset that includes programmes, projects and associated risks therein
I need to use a MAX function nested within some IF statements, but cannot seem to get it right.
basically.
where
the IMMEDIATE_PARENT_PROGRAM is null
the IS_PROGRAM = 1
STATUS_CODE is 'Open' or 'In Progress'
the TYPE_CODE = 'Risk'
the resulting data I then need to MAX so that I can find the highest RIM_PRIORITY_NUMBER
Can anybody help as i'm getting into a bit of a mess.
have attached a qvw wit it all in.
the two bottom text boxes was what I was starting with (the one on the right I created just to play around with a formula that works. It will eventually end up in the table
I think that it hard to handle null value with your current structure tables. I check data in table PROJECTS than notice that their no null value found.
You can test with this set analysis after you re-structure your table : =MAX({<STATUS_CODE={'Open','In Progress'},TYPE_CODE={'Risk'},IMMEDIATE_PARENT_PROGRAM={">=1"}>} RIM_PRIORITY_NUMBER)
I think that it hard to handle null value with your current structure tables. I check data in table PROJECTS than notice that their no null value found.
You can test with this set analysis after you re-structure your table : =MAX({<STATUS_CODE={'Open','In Progress'},TYPE_CODE={'Risk'},IMMEDIATE_PARENT_PROGRAM={">=1"}>} RIM_PRIORITY_NUMBER)
