Concat fields in set analysis

[nwong]

Hi,

Is there a way to concatenate the columns used in set analysis for a KPI?

I am trying to replicate the total figure in a KPI using the following formula:

=SUM(AGGR(COUNT({<CONTRACT_NUMBER={*}, ORDER_NUM={*}>} DISTINCT(ORDER_NUM)), PM_B_BUILDING_ID,PM_A_BUILDING_ID)))

 but I also need to enter a filter to include where 'A_COUNTRY_OHS AND B_COUNTRY_OHS' aren't empty.

If I do the below, it doesn't work as it reflects the rows which have either A or B country empty. I need it so that its where both of them are empty:

=SUM(AGGR(COUNT({<CONTRACT_NUMBER={*}, ORDER_NUM={*},A_COUNTRY_OHS={*}, B_COUNTRY_OHS={*}>} DISTINCT(ORDER_NUM)), PM_B_BUILDING_ID,PM_A_BUILDING_ID)))

Thanks

[stevejoyce · Accepted solution]

To or together you can have 2 sets in your set analysis and use + like this...

=SUM(AGGR(COUNT({

<CONTRACT_NUMBER={*}, ORDER_NUM={*},A_COUNTRY_OHS={*}>

+

<CONTRACT_NUMBER={*}, ORDER_NUM={*}, B_COUNTRY_OHS={*}>

} DISTINCT(ORDER_NUM)), PM_B_BUILDING_ID,PM_A_BUILDING_ID)))

[nwong]

This seems to work! Thanks a lot