Count distinct and aggr with set analysis

[userost123]

Hi everyone.

I have next 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

I would like to count distinct different CustomerIDs by ProductIDs where sum of Amount is different then zero. At the same time I would like to get result for current year (2019) and result for previous year (2018). So when I would choose Year=2019 in qlikview, I would like to get pivot table with next result:

CustomerID           Result for current year             Result for last year 
1                                     1                                                           2

2                                      1                                                          0

3                                      1                                                          0

With help from some of you I have found solution for current and previous year, but when I put set analysis in expression for previous year it doesn't work - it gives wrong result. Here is expression I use for previous year:

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

If some of you knows how to repair this expression that it would work for previous year I would be extremly happy, since I have tried really a lot of possibilities. Or if anyone has maybe idea how to get this thing work with any other expression?
Thanks in advance.

[Brett_Bleess]

Believe you will find what you need in the Design Blog area, here is link for AGGR with Set Analysis:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Another on on count v count distinct, not sure that is going to be of much use:

https://community.qlik.com/t5/Qlik-Design-Blog/Count-or-Count-distinct/ba-p/1469730

Believe the first one is the best bet, and there are a couple other links at the bottom of that post as well.  Best I can do for you, and my post will kick things back up, but cannot say if anyone else will chime in or not.

Regards,
Brett