topic Distinct buckets in QlikView

Distinct buckets

Mon, 12 Dec 2011 21:02:43 GMT

Hi,

I am trying to create a pie chart that contains a distinct set of buckets. The dataset looks like:

Object ID Value Date

1234 0 01/01/2011

1234 1 02/01/2011

1234 1 03/01/2011

1234 0 04/01/2011

3456 0 01/01/2011

5678 0 01/01/2011

The value field is set at the transaction level, i.e. an Object ID can have a value of 1 and 0, but at different dates.

So the result of the pie chart should be 3 distinct Object IDs:

- 1 Object ID (1234) in Bucket A, i.e. 33.33%

- 2 Object IDs in Bucket B, i.e. 66.66%

I.e. Because Object ID 1234 contains at least one Value of 1, I would like to place it in the seperate bucket.

Currently, based on the dimension =if([Value]=1, 'Bucket A', 'Bucket B') with expression =count(distinct [Object ID]) returns 1 for Bucket A and 3 for Bucket B, which is 4 (Object ID 1234 is pushed into both buckets because it also contains 0 Values. The total distinct population of Object IDs should be 3.

Please can you help.

Thanks,

Aminul

Distinct buckets

Mon, 12 Dec 2011 21:08:25 GMT

Try and see if this works as a dimension:

If(sum(aggr(Sum(Value),[Object ID]))>=1,'Bucket A','Bucket B')

Re: Distinct buckets

Mon, 12 Dec 2011 21:26:21 GMT

I'm afraid it chucks up an //Error in calculated dimension.

If I may, I would also like to do something similar for another field called Consecutive. This field basically counts the number of days the data in another field is stale for, i.e.

Object ID Example value Consecutive Date

5678 3 0 01/02/2011

9101 6 0 01/02/2011

3111 7 0 01/02/2011

3111 7 1 02/02/2011

3111 7 2 03/02/2011

3111 7 3 04/02/2011

3111 8 0 05/02/2011

So my query would be something along the lines of chuck all of the distinct Object IDs with a Consecutive of > 2 into a distinct bucket, and throw the rest into a seperate bucket, but don't count any object IDs that have already been put into the first bucket.

Distinct buckets

vgutkovsky — Mon, 12 Dec 2011 23:09:13 GMT

Small correction to the syntax above:

aggr(if(sum(Value)>=1,'Bucket A','Bucket B'),[Object ID])

Re: Distinct buckets

Tue, 13 Dec 2011 09:35:46 GMT

I'm afraid that only gives me back the total of the pie, with no breakdown of the buckets...

Re: Distinct buckets

vgutkovsky — Tue, 13 Dec 2011 15:45:14 GMT

See attached

Distinct buckets

Wed, 14 Dec 2011 19:49:30 GMT

Thanks Vlad! That worked.

I have a related question in regards to grouping. I am trying to create a bar chart, which highlights the number of values associated with various buckets: a set for where values are missing in my data and 1 bucket (called '0') to also show how many actual values I do have.

The dataset is:

Category Object_ID Date Value Null_Marker Null_Consecutive Null_Total

ABCD 1234 01/01/2011 5 0 0 4

ABCD 1234 02/01/2011 6 0 0 4

ABCD 1234 03/01/2011 7 0 0 4

ABCD 1234 04/01/2011 NULL 1 0 4

ABCD 1234 05/01/2011 NULL 1 1 4

ABCD 1234 06/01/2011 NULL 1 2 4

ABCD 1234 07/01/2011 NULL 1 3 4

ABCD 1234 08/01/2011 10 0 0 4

ABCD 1234 09/01/2011 10 0 0 4

ABCD 1234 10/01/2011 10 0 0 4

EFGH 5678 01/01/2011 12 0 0 0

EFGH 5678 02/01/2011 14 0 0 0

EFGH 5678 03/01/2011 15 0 0 0

EFGH 5678 04/01/2011 15 0 0 0

EFGH 5678 05/01/2011 15 0 0 0

EFGH 5678 06/01/2011 15 0 0 0

EFGH 5678 07/01/2011 15 0 0 0

EFGH 5678 08/01/2011 15 0 0 0

EFGH 5678 09/01/2011 11 0 0 0

EFGH 5678 10/01/2011 10 0 0 0

The expression is:

if(sum([Null_Marker])>0,sum([Null_Marker]),count([Null_Marker]))

Dimension:

=aggr(if(sum([Null_Marker])>0,

if(sum([Null_Marker])>1,

if(Sum([Null_Marker])>2,

if(Sum([Null_Marker])>4,

if(sum([Null_Marker])>9,

if(sum([Null_Marker])>14,

if(sum([Null_Marker])>39,

if(sum([Null_Marker])>64,

'65', '40'),

'15'),

'10'),

'5'),

'3'),

'2'),

'1'),

'0'),

[Object_ID])

It calculates the missing values correctly and creates the correct buckets, as per the data. Except, for the object where there the above conditions are met, it removes the whole object from the '0' bucket, i.e. which contains both Null Marker = 0 and Null Marker = 1 values. Please see screen shot below:

Re: Distinct buckets

vgutkovsky — Thu, 15 Dec 2011 05:30:24 GMT

It's a little confusing, but see attached. Is this what you were going for?

Regards,

Vlad

Re: Distinct buckets

Thu, 15 Dec 2011 13:17:13 GMT

Thanks Vlad. That's exactly it, but I'm trying this in version 11 and it doesn't work...

Re: Distinct buckets

Thu, 15 Dec 2011 13:41:15 GMT

I.e the zero bucket (actual) shows the same number as the Null bucket

Re: Distinct buckets

vgutkovsky — Thu, 15 Dec 2011 15:56:30 GMT

Whoa, that was unexpected and weird! I'm attaching a version that works in v11.

-Vlad

Re: Distinct buckets

Thu, 15 Dec 2011 19:59:52 GMT

Much better, but a few issues with this.

1) when i drill down to select 'NULL' bar, i.e. one bucket, it still displays the Zero bucket.

2) when i add in the category dimension, it creates a large space between the two current buckets, as if there was a third bucket there. I am planning on adding at least 3 additional dimensions, e.g. category, subcategory and year.

3) i can't drill down into the '0' bucket.

Re: Distinct buckets

vgutkovsky — Thu, 15 Dec 2011 20:42:58 GMT

Time to take your bucketing to the script, your UI will be too complicated otherwise. See attached.

For #2, that's too many dimensions for a bar chart, won't work. Try a straight or pivot table.

Regards,

Vlad

Re: Distinct buckets

Fri, 16 Dec 2011 19:51:35 GMT

That's sorted it, awesome! Many thanks!

Re point 2, I meant to say as a drill-down dimension.

Cheers!

Re: Distinct buckets

vgutkovsky — Fri, 16 Dec 2011 21:15:34 GMT

Please verify correct answers