topic Aggr function not zero not working in QlikView

Aggr function not zero not working

userost123 — Sat, 16 Nov 2024 02:18:13 GMT

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.

Re: Aggr function not zero not working

StarinieriG — Fri, 04 Oct 2019 15:07:17 GMT

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

Re: Aggr function not zero not working

userost123 — Sat, 05 Oct 2019 07:59:21 GMT

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.

Re: Aggr function not zero not working

asinha1991 — Sat, 05 Oct 2019 17:05:35 GMT

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

Re: Aggr function not zero not working

userost123 — Sun, 06 Oct 2019 07:33:29 GMT

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

Re: Aggr function not zero not working

userost123 — Mon, 07 Oct 2019 13:42:35 GMT

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.

Re: Aggr function not zero not working

StarinieriG — Mon, 07 Oct 2019 14:09:45 GMT

Hi,

you have to aggr by year too

Re: Aggr function not zero not working

asinha1991 — Mon, 07 Oct 2019 16:58:56 GMT

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

Re: Aggr function not zero not working

userost123 — Tue, 08 Oct 2019 06:15:18 GMT

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.