Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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

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)


aggregation_issue.jpg

Labels (1)
4 Replies
Highlighted

May be this

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

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

Highlighted

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

Highlighted
Partner
Partner

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?

image.png