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,

[sunny_talwar]

Isn't this what this is saying

There are 257 customers who have spent more than 3 million? and 644 customers who have spent between 500,000 and 3 million and so on.... When you add Amount as a dimension... a customer could have spent 10 one time and 50 million another time... he would then fall into two buckets... but if we sum it up, we see that the customer has spent over 50 million.....

[#3]

You are right about Sunny, I totally agree with you. but lets say I want to see how many customers have spent more than 3 million with me in detail. I would select that in my listbox and have a more detailed view of that to see who exactly the customers are, as below.

But as you can see on every row above, I am still getting zero (R0,00) amount falling within the >R3 Million Amount Bucket for some customers. It seems wrong to me. if the Amount_Bucket says >R3 Million then the Amount column should only be populated with records that are more than 3 Million.

I hope I'm making sense

[kamielrajaram]

Hi,

The reason this Customer 3A000261  shows under this 500 to 3mil bucket, is because the customers total spend fell between that range. By adding the amount in the the table, it will show each and every amount for the customer and all the amounts will be the 500 to 3 mil bucket, as that was the customers total spend, and the customer is counted once.

Kind regards

Kamiel

[kamielrajaram]

Hi Sello,Maybe remove the amount column from you dimension and change the Amount Expression to thisSUM(Total <[Customer ID]> Amount)

Kind RegardsKamiel

[sunny_talwar]

I guess I am still not completely sure what you are trying to get at... but if I select a single customer (3K000121)

His Sum(Amount) of made up of smaller Amounts... what are you hoping to see here? Individual amounts in different buckets even for a single customer?

[#7]

Oh Yes Sunny!!   I would like to see Individual amounts in different buckets for a single customer, could that be possible?

[#8]

I understand Kamiel and you're correct. would it be possible to have all amounts in different buckets for that particular customer though?

[kamielrajaram]

Hi, Using Sunny's calculated dimension, add Amount to the aggregation.

[#10]

That's what I am doing, check my initial/original post. Is that what you mean?

[kamielrajaram · Accepted solution]

Hi,

This is what I mean

=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],Amount)