Combine 2 dimensions to create a list of values

[hub]

Hello!

I am a bit stucked so maybe you may help 🙂

I have a list of projects A, B, C, D.

Each project has assigned a program value : program X, Y, Z.

Each project has an EAC value.

I would like to create a list of Program/Projects that are greater than a thresold, let's say 400. This is used as a dimension (a dimension for the "bar" in a bar graph).

Example :

• project A : EAC = 350 and program value is "program X"
• project B : EAC = 450 and program value is "program X"
• project C : EAC = 550 and program value is "program Y" or not assigned (value "<NoData>")

In that example, the code would return a list of values : program X (as 800 > 400) and project C (550 > 400). 

My code below is working more or less, however, there is strange behaviors. I have missing values. If I treat that a bit differently, I have other conflict (for example, the list would return "project B" as well, as it is an individual value above 400. Which is not good as this project is already embedded in the "program X" value, also listed...)

Any idea how to treat that in a better way than my code below ?

thanks !

=

if(

(
// test if there is a program assigned or not
[Project Program]<>' <NoData>'

AND

// shows the program names when the total EAC of the program is greater than 400M
aggr(

(
sum({$<[Cost Baseline Version]={"RF*"}
,[Project Tiering]={"Tier 1","Tier 2","Tier 3"},[Monthly Cost Destination]={'CAPEX','OPEX'}
>} EAC/1000000)
)

>400
,[Project Program])
)

,[Project Program],

// else if the program is not above 400M, check and show if any individual project is above 400M

if(

(
[Project Program]=' <NoData>'

AND

aggr(sum({$<[Cost Baseline Version]={"RF*"},[Project Tiering]={"Tier 1","Tier 2","Tier 3"},[Monthly Cost Destination]={'CAPEX','OPEX'}
>} EAC/1000000)>400,[Project Description])
)

,[Project Description]

))

[hub]

Anyone with a smart direction to help me ? 😅

[marcus_sommer · Accepted solution]

If I understand your aim right you are trying to create a single calculated dimension which has a hierarchically order and based on two calculated dimensions including some conditions. If so it has really some complexity because the levels will return independent values - which means they need to be associated to each other.

One way to approach the target may be to outsource the aggr() within variables - later you may replace them with adhoc-variables but to test the logic it would be helpful to see output directly and adjust it to the needed syntax. This might be:

v1:
= concat(aggr((
sum({$<[Cost Baseline Version]={"RF*"}, [Project Program] -= {'<NoData>'},
,[Project Tiering]={"Tier 1","Tier 2","Tier 3"},[Monthly Cost Destination]={'CAPEX','OPEX'}
>} EAC/1000000)
)>400,[Project Program]), chr(39) & ',' & chr(39))

and the same with the second aggr() and the final calculated dimension may then look like:

if(match([Project Program], '$(v1)', [Project Program],
if(match([Project Description], '$(v2)', [Project Description], 'Others'))

Just play a bit with the logic - means using the suggested dimensions without the variables just hard-coding a few [Project Program] + [Project Description] and if it goes in the right direction you could look on the variables and adjusting them until the result looks like the hard-coding.

[hub]

Hello Marcus!

Awsome, thanks I will test that !

Hubert 

[hub]

Whooooo, it works perfectly fine !

Thanks so much marcus, you rock 🙂

For the record and if it helps anyone, see the final code :

v_variableA

=

concat(

Aggr(

If(

(

(

            ((

          sum({$<[Cost Baseline Version]={"RF*"}

          ,[Project Tiering]={"Tier 1","Tier 2","Tier 3"},[Monthly Cost Destination]={'CAPEX','OPEX'}

          ,[Project Program] -= {'<NoData>'}

          >} EAC/1000000)

          )

          )>400                   

            )                         

)

            ,[Project Program]),[Project Program])

        , chr(39) & ',' & chr(39) 

     )

v_variableB

=

concat(

Aggr(

If(

(

  ((

          sum({$<[Cost Baseline Version]={"RF*"}

          ,[Project Tiering]={"Tier 1","Tier 2","Tier 3"},[Monthly Cost Destination]={'CAPEX','OPEX'}

//          ,[Project Program] -= {'<NoData>'}

          >} EAC/1000000)

          ))>400                        

)

        ,[Project Description]),[Project Description])

        , chr(39) & ',' & chr(39) 

     )

Combination in a dimension

=
if(match([Project Program],'$(v_variableA)')>0, [Project Program],
if(match([Project Description],'$(v_variableB)')>0, [Project Description],

'Others'))