Aggr function not zero not working

[userost123]

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.

[StarinieriG]

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

[userost123]

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. 

[asinha1991 · Accepted solution]

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

[userost123]

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

[userost123]

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.

[StarinieriG]

Hi,

you have to aggr by year too

[asinha1991]

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

[userost123]

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.