Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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.