Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

parthakk
Contributor II

Group by (Aggr in script)

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.

1 Solution

Accepted Solutions

Re: Group by (Aggr in script)

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

11 Replies
MVP
MVP

Re: Group by (Aggr in script)

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

MVP
MVP

Re: Group by (Aggr in script)

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)

Seems to be ok. What is wrong?

parthakk
Contributor II

Re: Group by (Aggr in script)

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

parthakk
Contributor II

Re: Group by (Aggr in script)

Hi Jonathan,

Attaching sample source and qvw file.

Thanks,

Partha K

parthakk
Contributor II

Re: Group by (Aggr in script)

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

Re: Group by (Aggr in script)

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

parthakk
Contributor II

Re: Group by (Aggr in script)

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

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.

Community Browser