Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
rougeherring
Contributor III
Contributor III

Pivot Table Set Analysis dividing sum by alternate record which must ignore dimension.

I am creating a Pivot Table that lists multiple records. 

Within a measure I need set analysis to divide a total based on 1 dimension by a total which is a unique record but must also utilize a second dimension. 

-The total is it's own separate record and cannot be a sum. 
-The pivot table has 1 dimension that must be included in the calculation. 

= Sum({$<[FlagOne]={1},[Market] = {"$(vMarket)"},[FlagTwo]={1},
[ProductID_Table] = {"$(=MaxString(ProductID_VariableGroup_ID))"}>}[Sales])

/

Sum(TOTAL{
$<[FlagOne]={1},[Market] = {"$(vMarket)"},[ThisRecordIsATotalFlag]={1},
[ProductID_Table] = {"$(=MaxString(ProductID_VariableGroup_ID))"}>}[Sales])

The numerator is working however the denominator is ignoring a dimension. There is another dimension in the pivot table (Product Type) that I need to be different for each group. How can I tell the total set analysis to give me the total based on a dimension in the Pivot Table.

Labels (1)
2 Replies
Kushal_Chawda

@rougeherring  add that dimension in total keyword. You can include your actual dimension name as I have assumed it

Sum(TOTAL <Product>{
$<[FlagOne]={1},[Market] = {"$(vMarket)"},[ThisRecordIsATotalFlag]={1},
[ProductID_Table] = {"$(=MaxString(ProductID_VariableGroup_ID))"}>}[Sales])

rougeherring
Contributor III
Contributor III
Author

Unfortunately that didn't work. I added the <[Dimension2]>  as <[Product]> after TOTAL and now it's calculating all zeros. 

The pivot table has 2 dimensions, one of which I have moved above the measures to group them ([Product]). Unfortunately, it is not breaking giving me a different total for each product based on that dimension. It's giving me the sum of all 4 products together.