Calculated Dimension Grouping issue

[#1]

Hi Family, I have been battling with grouping values using a calculated dimension. So I created a the dimension and specified a set of values I want displayed, and also created an expression which simply gives me a count of all my customers. The issue is that not all values in my calculated dimension are distributed into their correct buckets:

Dimension

=if(Aggr(Sum(Amount),[Customer ID])>=0 and Aggr(Sum(Amount),[Customer ID]) < 1,Dual('R0 - R0',1),

  if(Aggr(Sum(Amount),[Customer ID])>=1 and Aggr(Sum(Amount),[Customer ID]) <= 10000,Dual('R1 - R10,000',2),

  if(Aggr(Sum(Amount),[Customer ID])>=10001 and Aggr(Sum(Amount),[Customer ID]) <= 50000,Dual('R10,001 - R50,000',3),

  if(Aggr(Sum(Amount),[Customer ID])>=50001 and Aggr(Sum(Amount),[Customer ID]) <= 250000,Dual('R50,001 - R250,000',4),

  if(Aggr(Sum(Amount),[Customer ID])>=250001 and Aggr(Sum(Amount),[Customer ID]) <= 500000,Dual('R250,001 - R500,000',5),

  if(Aggr(Sum(Amount),[Customer ID])>=500001 and Aggr(Sum(Amount),[Customer ID]) <= 3000000,Dual('R500,001 - R3mil',6),

  if(Aggr(Sum(Amount),[Customer ID])>=3000001,Dual('> R3 milllion',7),'')))))))

Expression

Count([Customer ID])

For instance, Some of my amount values that are less than 3 million are showing in the 3 million buckets and visa versa. Urgently need help on this and very much appreciate all the help I can get.

Thanks,

[kamielrajaram]

Hi Sello,

Try this. Hope it helps.

Dimension

=  if(aggr(Sum(Amount),Customer ID)>=0 and aggr(Sum(Amount),Customer ID) < 1,dual('R0 - R0',1),

  if(aggr(Sum(Amount),Customer ID)>=1 and aggr(Sum(Amount),Customer ID) <= 10000,dual('R1 - R10,000',2),

  if(aggr(Sum(Amount),Customer ID)>=10001 and aggr(Sum(Amount),Customer ID) <= 50000,dual('R10,001 - R50,000',3),

  if(aggr(Sum(Amount),Customer ID)>=50001 and aggr(Sum(Amount),Customer ID) <= 250000,dual('R50,001 - R250,000',4),

  if(aggr(Sum(Amount),Customer ID)>=250001 and aggr(Sum(Amount),Customer ID) <= 500000,dual('R250,001 - R500,000',5),

  if(aggr(Sum(Amount),Customer ID)>=500001 and aggr(Sum(Amount),Customer ID) <= 3000000,dual('R500,001 - R3mil',6),

  if(aggr(Sum(Amount),Customer ID)>=3000001,dual('> R3 milllion',7),'')))))))

Expression

= count( if(aggr(Sum(Amount),Customer ID)>=0 and aggr(Sum(Amount),Customer ID) < 1,Customer ID,

  if(aggr(Sum(Amount),Customer ID)>=1 and aggr(Sum(Amount),Customer ID) <= 10000,Customer ID,

  if(aggr(Sum(Amount),Customer ID)>=10001 and aggr(Sum(Amount),Customer ID) <= 50000,Customer ID,

  if(aggr(Sum(Amount),Customer ID)>=50001 and aggr(Sum(Amount),Customer ID) <= 250000,Customer ID,

  if(aggr(Sum(Amount),Customer ID)>=250001 and aggr(Sum(Amount),Customer ID) <= 500000,Customer ID,

  if(aggr(Sum(Amount),Customer ID)>=500001 and aggr(Sum(Amount),Customer ID) <= 3000000,Customer ID,

  if(aggr(Sum(Amount),Customer ID)>=3000001,Customer ID,''))))))))

[sunny_talwar]

Try this and see if this helps

Dimension

=Aggr(

If(Sum(Amount) >= 0 and Sum(Amount) < 1, Dual('R0 - R0', 1),

If(Sum(Amount) <= 10000, Dual('R1 - R10,000', 2),

If(Sum(Amount) <= 50000, Dual('R10,001 - R50,000', 3),

If(Sum(Amount) <= 250000, Dual('R50,001 - R250,000', 4),

If(Sum(Amount) <= 500000, Dual('R250,001 - R500,000', 5),

If(Sum(Amount) <= 3000000, Dual('R500,001 - R3mil', 6),

If(Sum(Amount) >=3000001, Dual('> R3 milllion', 7),''))))))), [Customer ID])

Expression

Count(DISTINCT [Customer ID])

[#4]

Thanks guys for the feedback. Unfortunately, my chart is still not returning correct results.

[kamielrajaram]

Hi,

What are you dimensions beside the Amount bucket

Kind Regards

Kamiel

[sunny_talwar]

Would you be able to share a sample to show the issue?

[#7]

Well the 'Amount Bucket' is a calculated dimension I created using the 'Amount' dimension.

Basically, I think I might only need to use these 2 dimensions for my solution:

      1. Customer ID (In my expression to get a count of my customers)

      2. Amount & Customer ID (In my Calculated dimension as part of my Aggr function).

Those are the only 2 dims I am using. The other ones that I have are Customer_Name, Created_Date, Paid_Date, and Customer_Status. I doubt I would need these dimensions to solve this though.

Thank you

[#8]

Please see a sample attached !

[sunny_talwar]

Are you trying to get to this?

Removed Amount as dimension and used Sum(Amount) as an expression

[#10]

Not really, I am actually trying to get total number of customers that fall within each Amount bucket.

For instance:-

              A count of customers that have spent more than 3 Million with me,

              A count of customers that have spent between  500K and 3 Million with me,

              A count of customers that have spent between  250K and 500K with me, and so forth...

As you can see below when I add all dimensions ,most amounts are distributed to wrong amount buckets.