Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Count distinct and aggr with set analysis

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.

1 Reply
Highlighted
Digital Support
Digital Support

Re: Count distinct and aggr with set analysis

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.