Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
mendes094
Contributor III
Contributor III

Create a master item dimension with conditions

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.

Labels (1)
4 Replies
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

Read more at Data Voyagers - datavoyagers.net
Follow me on my LinkedIn | Know IPC Global at ipc-global.com

mendes094
Contributor III
Contributor III
Author

@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.

Read more at Data Voyagers - datavoyagers.net
Follow me on my LinkedIn | Know IPC Global at ipc-global.com

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