Retrieve/match dimension label in a pivot table

[therealdees]

Hi!

I'm trying to display different values for a pivot table for the different dimension combination the user sets up by drag and drop.

I currently have 3 different dimensions as rows, but when trying to use GetObjectField(n) or GetObjectDimension(n) nothing is returned, but null...

Should these functions work normally in a pivot table or are the limited to straight tables? What am I missing?

In the picture above the first dimension is a grouped/concatenated "key" (ex. ADIDAS|UNISSEX) (not a calculated dimension, but built in script level), the second dimension is the store name (ex. "BSB") and the third dimension is the brand name (ex. "Adidas").

What I'm trying to do is sum the "Qtd. Referencias" (SKU count) only if dimension 1 = "store name" (loja_sigla), otherwise it should calculate the average. The expression I tried to use is the following, although it doesn't retrieve the dimension label or index:

If(GetObjectField(1) = loja_sigla or Dimensionality() = 0 or Dimensionality() = 3,
//THEN
Sum(Aggr(Count({1<key_deposito = {'1', '1 - ESTOQUE'}, estoque_quantidade = {">=1"}, estoque_data = {'$(=MonthEnd(Max(data)))'}>} Distinct produto_referencia), loja_sigla, produto_agrupamento_genero, produto_marca)),

//ELSE
Sum(Aggr(Count({1<key_deposito = {'1', '1 - ESTOQUE'}, estoque_quantidade = {">=1"}, estoque_data = {'$(=MonthEnd(Max(data)))'}>} Distinct produto_referencia), loja_sigla, produto_agrupamento_genero, produto_marca))
/
Sum(Aggr(Count({1<key_deposito = {'1', '1 - ESTOQUE'}, estoque_quantidade = {">=1"}, estoque_data = {'$(=MonthEnd(Max(data)))'}>} Distinct loja_sigla), loja_sigla, produto_agrupamento_genero, produto_marca))
)

This is just the context of what I'm trying to do, but I guess it doesn't matter that much. The problem is GetObjectField or GetObjectDimension doesn't work, regardless the index number I use...

Any help is appreciated

[Or · Accepted solution]

I'm not aware of any issues with these functions in a pivot. I'd suggest you check the rest of your formulas for potential issues and/or break them down into parts and check each part.

[therealdees]

Hey! Thanks for the reply

I was trying to use GetObjectDimension, but didn't realize it delivers the label for the dimension, not the dimension name itself.

It worked now, thanks for the tip!! 😁