2 Replies Latest reply: Oct 4, 2013 4:44 AM by Gysbert Wassenaar

# Problem with AGGR Function incl. Set-Analysis in Pivot Chart

Hi!

I have a Pivot Chart in which I need to display the number of customers whose support period runs out in the selected quarter.My direct expression (using the Customer dimension as an active dimension) works fine - all relevant customers are listed.

count({\$<Quarter=, SupportEndQuarter={"\$(=only(Quarter))"}>}distinct(CustomerID))

The goal however is that the information summarized by sales region - the customer dimension cannot be used. I tried this:

sum (aggr(count({\$<Quarter=, SupportEndQuarter={"\$(=only(Quarter))"}>}distinct(CustomerID)),CustomerID))

But this does not capture the majority of customers. I tried the expression also WITH the CustomerID as an active dimension - still, only 10 % of the customers show.

Many Thanks!

• ###### Re: Problem with AGGR Function incl. Set-Analysis in Pivot Chart

What is it that you are trying to aggregate using Sum expression???

If I understand the problem correctly, try removing Sum and Count from the expression:

=aggr(only{\$<Quarter=, SupportEndQuarter={"\$(=only(Quarter))"}>}distinct(CustomerID)),CustomerID))

• ###### Re: Problem with AGGR Function incl. Set-Analysis in Pivot Chart

If you want a summary per sales region then you'll have to use that field in the aggr function:

sum(aggr(count({\$<Quarter=, SupportEndQuarter={"\$(=only(Quarter))"}>}distinct(CustomerID)),SalesRegion))