# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

New Contributor II

## Aggr function not zero not working

Hello everyone,

I have next  example of data set:

CustomerID               ProductID                     Amount

1                                      P1                                    2

1                                      P2                                    0

1                                      P3                                    -5

2                                      P1                                    3

2                                      P2                                   1

2                                     P2                                  - 1

3                                      P1                                    3

3                                      P2                                    1

I would like to get pivot table in QlikView, which would count number of different CustomerIDs by ProductID, where Amount is different then 0.  I need to get pivot table with next result:

ProductID               Result

P1                                3

P2                                1

P3                                1

I use pivot table with dimension ProductID and expression:

=count (distinct ( if( aggr( sum(Amount), CustumerID,ProductID) <> 0, CustomerID)))

With this expression I get the wrong result. It counts also customer, where sum of Amount is 0.

The result I get is

ProductID               Result

P1                                3

P2                                2

P3                                1

If someone could help with this I would be very grateful. If someone knows some solution even withouth aggr function it would be even better. Thanks in advance.

Tags (2)
Labels (5)

• ### Set Analisys

1 Solution

Accepted Solutions
Contributor III

## Re: Aggr function not zero not working

If I understand you correct, if sum of amount =0 for a combination of product and customer than it should not be counted?

maybe try this?

count (distinct  aggr( if(sum(Amount)<>0,CustumerID), CustumerID,ProductID) )

8 Replies
Partner

## Re: Aggr function not zero not working

Hi,

why this result

ProductID               Result

P1                                3

P2                                2

P3                                1

is wrong?

P1 has 1,2,3

P2 has 2,3

P3 has 1

New Contributor II

## Re: Aggr function not zero not working

You are completly right. I mistyped the data set here, I edited the question. I hope it is more clear now where is the problem. If you could help, I would appreciate very much.

Contributor III

## Re: Aggr function not zero not working

If I understand you correct, if sum of amount =0 for a combination of product and customer than it should not be counted?

maybe try this?

count (distinct  aggr( if(sum(Amount)<>0,CustumerID), CustumerID,ProductID) )

Highlighted
New Contributor II

## Re: Aggr function not zero not working

Thank you so much. This works exactly as I needed.

New Contributor II

## Re: Aggr function not zero not working

I have another question regarding this issue and I would be very thankful if you or someone else could maybe help.

I have added another column to my data set:

CustomerID               ProductID                     Amount                               Year

1                                     P1                                      2                                              2019

1                                      P2                                    0                                               2019

1                                      P3                                    -5                                             2019

2                                      P1                                    3                                              2018

2                                      P2                                   1                                                2019

2                                     P2                                  - 1                                              2019

3                                      P1                                    3                                             2018

3                                      P2                                    1                                             2019

If I make a choice in qlikview : Year = 2019 I would like to get pivot table with next result:

ProductID               Result for current year             Result for previous year

P1                               1                                                                        2

P2                                1                                                                       0

P3                                1                                                                        0

So,  logic is the same as for previous issue that I had, but now I just added another dimension -> year.

First expression (Result for current year) is:

count (distinct  aggr( if(sum(Amount)<>0,CustumerID), CustumerID,ProductID)),

but second expression (to get result for previous year) would need some set analysis.

I try something like:

count (  {    distinct  aggr( if(sum(Amount)<>0,CustumerID), CustumerID,ProductID))

If you maybe know how to help, I would appreciate very much. Thanks.

Partner

## Re: Aggr function not zero not working

Hi,

you have to aggr by year too

Contributor III

## Re: Aggr function not zero not working

is it that you are always going to select only 1 year and want to see that year and previous year?

you can add set analysis in sum of aggr

current year

count (distinct aggr( if(sum({\$<Year={"\$(=Max(Year))"}>}Amount)<>0,CustumerID), CustumerID,ProductID))

previous year:

count (distinct aggr( if(sum({\$<Year={"\$(=Max(Year)-1)"}>}Amount)<>0,CustumerID), CustumerID,ProductID))

New Contributor II

## Re: Aggr function not zero not working

Thanks for suggestion, but it is unfortunatelly not working well for previous year. I get result 0 in every row, which is not ok.
Expression is OK, but I don't know why the result I get doesnt take into account previous year.