topic Re: Group by (Aggr in script) in QlikView

Group by (Aggr in script)

parthakk — Mon, 26 Jan 2026 18:19:17 GMT

Hi Everyone,

I am having member_id's who have done multiple transaction within a month. I want to know the count of distinct member_id's who have done transaction for each month.

I have written script as

Aggregate:

LOAD Month,

Count(DISTINCT Member_Id) as Member_Count,

Sum(Sales) as Sum_Sales

FROM

[..\Aggr_Temp.qvd]

(qvd)

Group by

Month;

Sales as is summed up, i am getting proper value but member_id count is going wrong.

Kindly suggest.

Attaching a sample file.

Re: Group by (Aggr in script)

jonathandienst — Fri, 20 May 2016 11:12:04 GMT

Well your syntax is good, why do you believe that it is wrong? What do you get and what are you expecting to get?

Re: Group by (Aggr in script)

jonathandienst — Fri, 20 May 2016 11:13:15 GMT

And it would be better to attach the qvw producing the "wrong" result. The source data is not that useful in addressing this query.

Re: Group by (Aggr in script)

Kushal_Chawda — Fri, 20 May 2016 11:15:41 GMT

Seems to be ok. What is wrong?

Re: Group by (Aggr in script)

parthakk — Fri, 20 May 2016 11:21:35 GMT

Hi Jonathan,

I had taken sample data with distinct 20 Member_Id's. However when i take the sum(Member_Count) in a text box i am getting value as 23. Which should be 20.

Thanks,

Partha K

Re: Group by (Aggr in script)

parthakk — Fri, 20 May 2016 11:52:08 GMT

Hi Jonathan,

Attaching sample source and qvw file.

Thanks,

Partha K

Re: Group by (Aggr in script)

parthakk — Fri, 20 May 2016 12:11:03 GMT

When i take member count its showing wrong. Attached sample source and qvw file

Re: Group by (Aggr in script)

rubenmarin — Fri, 20 May 2016 12:49:37 GMT

Hi Partha,If Concept and Territory are included in the group by, it will count the same Member_Id for each concept and territory it appears

Re: Group by (Aggr in script)

parthakk — Sat, 21 May 2016 03:21:14 GMT

‌Hi Ruben

I got your point. So how do i calculate distinct member_id's now??

Thanks,

Partha K

Re: Group by (Aggr in script)

rubenmarin — Mon, 23 May 2016 07:23:23 GMT

Hi Partha, the script in your original post will return the desired result, it will store the count of different Member_Id in each month. But if you want something different, adding territory and concept, I will need an update of the requirements.

On the other side, by your post below... you have 18 different Member_Id in Feb and 5 in Jan. If you do a Sum of Member_Count in this months it will return 23. Having it precalculated in script you can't know how many of the Member_Id in Jan are repeated in Feb.

If you want it to count '20' I think you'll need to calculate the "Count(DISTINCT Member_ID)" in front-end. As it will count depending on selections and dimensions.

Re: Group by (Aggr in script)

parthakk — Mon, 23 May 2016 11:29:05 GMT

Hi Ruben,

Thank you for suggestion. However i cannot calculate "Count(DISTINCTMember_ID)", because i shall be dropping my Member_Id column itself. I have data in millions of records at day level. I shall be aggregating data at month level so i just need distinct member count at month level. This is attained when a month is selected

ex: 5 in Jan and 18 in Feb.

If no month is selected then sum goes wrong. So how do i achieve this?

Thanks,

Partha K

Re: Group by (Aggr in script)

rubenmarin — Mon, 23 May 2016 12:44:37 GMT

Hi Partha, sorry but I don't see a way to make the count based on selections having it previously calculated in script.