Group by (Aggr in script)

[parthakk]

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.

[jonathandienst]

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

[jonathandienst]

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

[Kushal_Chawda]

Seems to be ok. What is wrong?

[parthakk]

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]

Hi Jonathan,

Attaching sample source and qvw file.

Thanks,

Partha K

[parthakk]

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

[rubenmarin]

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]

‌Hi Ruben

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

Thanks,

Partha K

[rubenmarin]

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.