6 Replies Latest reply on Feb 21, 2018 6:41 AM by Olalekan Agboluaje

# Create Groups with over 1000 records and calculate measures

Hi All

Like below, How do I put 2000 customer Nos into Group A, 5000 into Group B and 7000 into Group C and be able to calculate the sales per month as above?

Group value will come form 10,000 customer numbers which is in this format( 001, 002, 003, 004…….). finding it difficult to create the groups let alone the calculations.

It is easy to create a group with few Dimensions but not with hundreds or thousands of Dimension.

 Sales per month Month/Year Group A Group B Group C Jan-17 ABC ABC ABC Feb-17 ABC ABC ABC Mar-17 ABC ABC ABC Apr-17 ABC ABC ABC May-17 ABC ABC ABC Jun-17 ABC ABC ABC Jul-17 ABC ABC ABC Aug-17 ABC ABC ABC Sep-17 ABC ABC ABC Oct-17 ABC ABC ABC Nov-17 ABC ABC ABC Dec-17 ABC ABC ABC

Thanks

Ola

• ###### 1. Re: Create Groups with over 1000 records and calculate measures

Hi, Ola

Have you tried similar calculation as below

if Int([Customer No]) <=2000 then

"Group A"

elseif Int([Customer No]) <=5000 then

"Group B"

elseif Int([Customer No]) <=7000 then

"Group C"

else

"Group D"

end

ZZ

• ###### 2. Re: Create Groups with over 1000 records and calculate measures

Hi ZZ

Thanks for your answer but that is not what i was looking for, probably the way i framed the question.

I have a list of over 30,000 customer numbers, i want to create 3 separate groups (not set) with each having at least over 1000 customers in them. i was able to create sets but not groups because you can copy and paste the list of customer numbers into sets but you cant do that with a group.

in the end, i want to show each group as shown in my first questions.

Hope this is much clearer?

Thanks

Ola

• ###### 3. Re: Create Groups with over 1000 records and calculate measures

Hi, Ola

Do you have any sample data to share? Don't necessary to make it 30,000, just 30 and we can group by 10 of each for a group as example.

and what's the rules which customer should be in which group?

ZZ

• ###### 4. Re: Create Groups with over 1000 records and calculate measures

Hi ZZ,

Please find attached a sample data set. would it be possible to have at least 5000 members in each group. Also if its possible to copy and paste members into the group.

For example, if a group already has 5000 customer No and i want to add another 1000 customer no to that group.

• ###### 5. Re: Create Groups with over 1000 records and calculate measures

Hi, Ola

Thanks for the workbook, although a bit of huge crashing my laptop

Please find my solution below by grouping cust No into 3 groups, but you can change based on your own, just need change the % 3 into % n, n is the number of groups. Any new cust no coming, will be distributed to these groups automatically without any copy/paste or override calculation.

I can't attached back your workbook since it is too big. but let me know if you have quesiton

Hope this helps

ZZ

• ###### 6. Re: Create Groups with over 1000 records and calculate measures

Hi ZZ,

I'm afraid this doesn't work for me. The customer numbers in each group are random customers and not in sequence.

The group is based on different factors and criteria and sometimes you have customer numers appearing in more than one group or all groups.

Your solution above seems to work but i want to decide what customer number goes into what group. for example, if i want to set up a new group with 508 customer numbers how can i achieve this. Please note, i am able to create a set easily but not a group. or is there a way to turn a set into a group?

Please feel free to delete records in the workbook if you need to reduce the size