Create a master item dimension with conditions

[mendes094]

Hello,
I need create a master item dimension that use some conditions, I try to do this code but does'n work.
=IF(SUM(ABASTECIMENTO_COTA) <= 0, 'Não, Sem cota',
IF(AVALIACAO = 'Extra meta', 'Avaliar',
IF(SUM(ESTOQUE_IDEAL_COTA) - (TRANSITO_AB+PROGRAMADO_AB+POSICIONADO) <= 0, 'Não','Sim'
)))
When I put this dimension in a graphic the menssage: "Invalid dimension” appears. Anybody can help me?

Tks.

[marksouzacosta]

Hi @mendes094,

The problem is that you are using aggregation functions inside Master Dimensions. Sum, Avg, Count, etc are intended to be used inside Measures and Master Measures.

If possible, I would recommend moving this logic to the Load Script, creating a new field there.

Regards,

Mark Costa

[mendes094]

@marksouzacosta 

How I moving this logic to the Load Script?

tks.

[marksouzacosta]

Hi @mendes094 ,

I would need a copy of your Load Script to help but, will be something like this - if you have all fields in the same table:

LOAD

// your fields

IF(SUM(ABASTECIMENTO_COTA) <= 0, 'Não, Sem cota',
IF(AVALIACAO = 'Extra meta', 'Avaliar',
IF(SUM(ESTOQUE_IDEAL_COTA) - (TRANSITO_AB+PROGRAMADO_AB+POSICIONADO) <= 0, 'Não','Sim'
))) AS MyNewField

FROM
// YourDataSource
;

There is catch here. Since you have aggregation functions such as SUM(ABASTECIMENTO_COTA) you have to pre-calculate that somewhere in your Load Script and join that to your Main table.

[Kushal_Chawda]

@mendes094  If you use aggregation functions in dimension you will need to wrap the condition inside aggr . See more about aggr function here

=aggr(IF(SUM(ABASTECIMENTO_COTA) <= 0, 'Não, Sem cota',
IF(match(AVALIACAO ,'Extra meta', 'Avaliar'),
IF(SUM(ESTOQUE_IDEAL_COTA) - (sum(TRANSITO_AB)+sum(PROGRAMADO_AB)+sum(POSICIONADO)) <= 0, 'Não','Sim'
))), Dim1)

where Dim1 is dimension you want to display on which grouping will be performed