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

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

Fri, 04 Oct 2013 06:54:21 GMT

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

Fri, 04 Oct 2013 07:02:29 GMT

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

Gysbert_Wassenaar — Fri, 04 Oct 2013 08:44:58 GMT

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))