Show values disregarding other dimensions in table

[therealdees]

Hi!

I have concatenated the "current price" to a fact table that holds Sales facts and Inventory facts.

The current price was concatenated holding only values for the product_key and price itself. What I need to do is show the current price for every SKU displayed in the table, disregarding if it's dimensioned by other fields.

It works perfectly except when I select "stores" as dimension. Probably because the same SKU is related to stores but in sales and inventory level.

The closest I got was with this expression, but it aggregates (obviously) by store and shows only 1 value per SKU (even if there's the same SKU printed for different stores):

Aggr(Only({1<fato_origem = {'Preço Atual'}>} produto_preco_atual), key_produto_sku)

Here's the expected result, except it's working only without "stores" as a dimension

Here's the value being displayed only once per SKU

Any ideas of how I could achieve this? I'm afraid this must be simple... Not sure of what I'm missing here

[Oleg_Troyansky · Accepted solution]

Hi there!

This kind of use of AGGR (by SKU only, in a chart by SKU and store), doesn't work properly because it violates "The Third Law of AGGR" (you need to read my book or listen to my AGGR lecture at the Masters Summit for Qlik) to learn more about it).

For your specific issue, I can offer a number of options:

1. First of all, why concatenate prices to the Concatenated Fact table? Are these prices changing over time? If you only have one price per SKU, why not simply join the prices to the fact table, and have that information available, both for Sales and Inventories? This would be the best solution in my opinion.

2. In a table where SKU is one of the dimensions, you can simply use TOTAL to get out of the dimensionality, like this:

min( TOTAL <SKU> Price) - this will give you the lowest price per SKU. You may replace min() with any other aggregation function.

2. If you insist on using AGGR (I wouldn't...), add a NODISTINCT qualifier, and it should produce the desired result.

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

[therealdees]

Oleg,

I tried everything you said above and the only thing that worked properly was using No Distinct....

I'm not sure why... I'm no expert and I do understand the concept behind it, but when joining the prices instead of concatenating, it was giving me some blank values still, although most cases were working...

I was joining using only the product_key and product_price. For what I understand, that should aggregate the price for every instance of the product_key found within the fact table, isn't so?

This is the expression that gave me every price for every SKU without any blanks:

Aggr(NoDistinct Only({1} produto_preco_atual), key_produto_sku)

[therealdees]

Aff

Everything is almost done, but I just can't make the Totals calculate correctly a markup measure where I use the joint prices 😫

The column "Preço Orig. Total" is multiplying the sold amount by the original price that I added with the join you suggested. The total in this column is correct but I forced sum changing the "Totals" behavior from "auto" to "sum". In another column, "Markup Original", I use the same values but I divide it by the values from "Custo Total". It works fine but not in the totals row...... Probably because the resulting value is an array or something likely.

How can I fix this??? Been hours running in circles

Expression used in the Preço Orig. Total:

Aggr(NoDistinct Only({<app_soma_nf_flag = {'S'}, erp_venda_devolucao_flag = {'V'}>} produto_preco_original), key_produto_sku)
*
PecasQuantidade //This is a master item calculating the sold amount and should work fine as it's just a sum expression with a set analysis to get it from sales

The expression used in the "Markup Original":

(Only(Total<key_produto_sku> Aggr({<app_soma_nf_flag = {'S'}, erp_venda_devolucao_flag = {'V'}>} produto_preco_original, key_produto_sku, loja_sigla))
*
PecasQuantidade)
/
CustoTotal // this is a master item to calculate the total cost for the sold products and should also work fine as it's just a sum with set analysis as well

[therealdees]

I guess I was complicating everything 'cause this expression did the job:

(Sum(Aggr(Sum({<app_soma_nf_flag = {'S'}, erp_venda_devolucao_flag = {'V'}>} erp_quantidade_venda) * produto_preco_original, loja_sigla, key_produto_sku))
/
CustoTotal

I tried so many random things that I'm not even sure, but I guess I wasn't considering the store in the aggregation at all

Thanks @Oleg_Troyansky , once again your help was very useful