Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
crvctsaa
Contributor II
Contributor II

Sum Aggr whilst ignoring dimensions in pivot

Hello everyone, I've configured a Vizlib pivot to use filter boxes for adding and removing dimensions and hence using concat in my expression to add the correct aggregations based on filter box selections.

There are 2 versions of this pivot, the first version is working perfectly as a simple straight average is needed. The working measure expression for the first pivot is:

avg({$<_CalendarType={'current'},model_type={'Pre-Built'},vendor_column={'$(=FirstSortedValue(target_vendor,target_vendor_sort_weight))'}>}total <$(=concat(row_dimension_value,','))> VendorListPrice)

 

Capture - Product.PNG

The desired result here is correct as I am able to have the Dell totals appear under Hewlett Packard and so on - these totals are required for later calculations which are not relevant for this posting.

 The second version of the pivot I cannot get to work, the results of this expression should be the sum of aggregated averages. So far the expression I have is:

(sum({$<_CalendarType={'current'},model_type={'CTO Config'}>}
Aggr(
avg({$<_CalendarType={'current'},model_type={'CTO Config'},vendor_column={'$(=FirstSortedValue(target_vendor,target_vendor_sort_weight))'}>} TOTAL <$(=concat(cto_row_dimension_value,','))> VendorListPrice*CONFIG_ITEM_COUNT)
,$(=concat(cto_row_dimension_value,',')) )))

This adds the correct total to the Dell column but this same total is needed under the Cisco column for example. I have tried to use a total qualifier in the outer sum expression to ignore dimensions without success.

Capture - Configurations.PNG

Hopefully this is enough information to go by, any help would be greatly appreciated !

1 Solution

Accepted Solutions
crvctsaa
Contributor II
Contributor II
Author

I was able to achieve the desired result by using a TOTAL qualifier inside the Sum of the aggr.

(sum(TOTAL <$(=concat(cto_row_dimension_value,','))>
Aggr(
avg({$<_CalendarType={'current'},model_type={'CTO Config'},vendor={'$(=FirstSortedValue(target_vendor,target_vendor_sort_weight))'}>} VendorListPrice*CONFIG_ITEM_COUNT)
,$(=concat(cto_row_dimension_value,',')))))

I realise now that the set analysis was totally redundant inside of an aggr.  😊

View solution in original post

3 Replies
lironbaram
Partner - Master III
Partner - Master III

hi 

you need to add nodistinct to your expression 

(sum({$<_CalendarType={'current'},model_type={'CTO Config'}>}
Aggr(nodistinct
avg({$<_CalendarType={'current'},model_type={'CTO Config'},vendor_column={'$(=FirstSortedValue(target_vendor,target_vendor_sort_weight))'}>} TOTAL <$(=concat(cto_row_dimension_value,','))> VendorListPrice*CONFIG_ITEM_COUNT)
,$(=concat(cto_row_dimension_value,',')) )))

 

crvctsaa
Contributor II
Contributor II
Author

Hi, thanks for your response. Unfortunately that's not worked. 🙂 Here is the result of the nodistinct.

crvctsaa_1-1597735617607.png

 

crvctsaa
Contributor II
Contributor II
Author

I was able to achieve the desired result by using a TOTAL qualifier inside the Sum of the aggr.

(sum(TOTAL <$(=concat(cto_row_dimension_value,','))>
Aggr(
avg({$<_CalendarType={'current'},model_type={'CTO Config'},vendor={'$(=FirstSortedValue(target_vendor,target_vendor_sort_weight))'}>} VendorListPrice*CONFIG_ITEM_COUNT)
,$(=concat(cto_row_dimension_value,',')))))

I realise now that the set analysis was totally redundant inside of an aggr.  😊