Need help with aggregation function

[#1]

Hi all,

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

Thanks in advance,

Matt

[Sokkorn · Accepted solution]

Hi Matt,

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)

Regards,

Sokkorn

[#3]

Hi Sokkorn,

Thank you so much for your help

I havent ever tried including expressions like that within those functions - and always seem to end up embedding loads of IF statements..

Seems to work like a charm

Thanks again,

Matt