topic Re: Calculated Dimension Grouping issue in QlikView

Calculated Dimension Grouping issue

Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Calculated Dimension Grouping issue

kamielrajaram — Mon, 24 Apr 2017 09:42:23 GMT

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,''))))))))

Re: Calculated Dimension Grouping issue

sunny_talwar — Mon, 24 Apr 2017 09:44:48 GMT

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])

Re: Calculated Dimension Grouping issue

Mon, 24 Apr 2017 09:58:41 GMT

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

Re: Calculated Dimension Grouping issue

kamielrajaram — Mon, 24 Apr 2017 10:37:44 GMT

Hi,

What are you dimensions beside the Amount bucket

Kind Regards

Kamiel

Re: Calculated Dimension Grouping issue

sunny_talwar — Mon, 24 Apr 2017 10:51:42 GMT

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

Re: Calculated Dimension Grouping issue

Mon, 24 Apr 2017 10:52:12 GMT

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

Re: Calculated Dimension Grouping issue

Mon, 24 Apr 2017 11:15:35 GMT

Please see a sample attached !

Re: Calculated Dimension Grouping issue

sunny_talwar — Mon, 24 Apr 2017 11:19:17 GMT

Are you trying to get to this?

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

Re: Calculated Dimension Grouping issue

Mon, 24 Apr 2017 11:35:42 GMT

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.

Re: Calculated Dimension Grouping issue

sunny_talwar — Mon, 24 Apr 2017 11:41:42 GMT

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.....

Re: Calculated Dimension Grouping issue

Mon, 24 Apr 2017 11:58:49 GMT

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

Re: Calculated Dimension Grouping issue

kamielrajaram — Mon, 24 Apr 2017 11:59:14 GMT

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

Re: Calculated Dimension Grouping issue

kamielrajaram — Mon, 24 Apr 2017 12:12:23 GMT

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

Kind RegardsKamiel

Re: Calculated Dimension Grouping issue

sunny_talwar — Mon, 24 Apr 2017 12:17:04 GMT

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?

Re: Calculated Dimension Grouping issue

Mon, 24 Apr 2017 12:57:56 GMT

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

Re: Calculated Dimension Grouping issue

Mon, 24 Apr 2017 12:59:38 GMT

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

Re: Calculated Dimension Grouping issue

kamielrajaram — Mon, 24 Apr 2017 13:05:47 GMT

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

Re: Calculated Dimension Grouping issue

Mon, 24 Apr 2017 13:09:06 GMT

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

Re: Calculated Dimension Grouping issue

kamielrajaram — Mon, 24 Apr 2017 13:13:24 GMT

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)