Sum Aggr whilst ignoring dimensions in pivot

[crvctsaa]

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)

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.

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

[lironbaram]

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]

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

[crvctsaa · Accepted solution]

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