topic Aggregate by and repeat its total for instance of a dimension value (Pivot Table) in App Development

Aggregate by and repeat its total for instance of a dimension value (Pivot Table)

JSDEV — Fri, 09 Aug 2019 14:04:22 GMT

We're struggling trying to achieve something that may not even be possible.

See screenshot attached where I have mocked the data for the current and expected results.

For all instances of a product, we need to repeat the overall "total unit" sold for that product without affecting the grand total:

"Kinder Bueno" = 21 (13 + 8 )
"Cheese & Ham Panini" = 5 (5)
Grand total = 26 (21 + 5)

Are we missing anything in the expression? With "Aggr" it didn't work either.

sum({<fact_type={'facts_product'}>} total <Category, Product> units_sold)

Re: Aggregate by and repeat its total for instance of a dimension value (Pivot Table)

sunny_talwar — Fri, 09 Aug 2019 13:56:24 GMT

May be this

If(Dimensionality() = 0, Sum({<fact_type = {'facts_product'}>} units_sold), Sum({<fact_type = {'facts_product'}>} TOTAL <Category, Product> units_sold) )

Re: Aggregate by and repeat its total for instance of a dimension value (Pivot Table)

JSDEV — Fri, 09 Aug 2019 14:42:35 GMT

@sunny_talwar , thanks the reply. We do use "Dimensionality() = 0" (sorry for not posting the full formula).

We noticed it works when the product filter is selected but not the other way around. I'll try to mock what's happening in my next reply.

Re: Aggregate by and repeat its total for instance of a dimension value (Pivot Table)

sunny_talwar — Fri, 09 Aug 2019 14:50:33 GMT

I think a simple sample might help us see what you have and you can point all the numbers which needs to look different.

Re: Aggregate by and repeat its total for instance of a dimension value (Pivot Table)

JSDEV — Thu, 15 Aug 2019 10:56:22 GMT

Hi @sunny_talwar

Apologies for the delay, we were busy trying to fix that by changing the way the fact table was structure and we've had no success.

See update sampled below which also mocks our transacional and fact tables. I've omitted other measures and left just one product to keep things a simple.

The main driving dimension is the one named "Special" but the report also needs to be sliceable by "Category", "Product" and "Store" dimensions.

For every instance of the "Cheese and & Ham", we expect to see 13 units sold as seen in the "Transacional: Sales" table but the result of the aggregation is double counted.

Any idea how to fix that through Qlik Sense aggregations? Any other suggestion?