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.

[parthakk]

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

[rubenmarin · Accepted solution]

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