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
Count(DISTINCT Member_Id) as Member_Count,
Sum(Sales) as Sum_Sales
Sales as is summed up, i am getting proper value but member_id count is going wrong.
Attaching a sample file.
Solved! Go to Solution.
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.
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.