topic Re: Re: Dynamic Bucket Value Based on Calculated Percentage in QlikView

Dynamic Bucket Value Based on Calculated Percentage

Fri, 18 Jul 2014 19:31:26 GMT

I created a straight table with one of my expression columns being an aggregated average calculation based on a start and end date selected by the user. Using that percentage value, I am trying to calculate and display a bucket category based on whether it falls between a low and high range defined in a lookup table.

Example of what the output should be:

Firm	Role	Underlying	Compliance %	Bucket
Company abc	DPM	XYZ	95.123	A
Company def	MM	ZYX	92	B

The expression for Compliance % is :

=Aggr( AVG( if (TRADE_DATE >= $(vStartDate) AND TRADE_DATE <= $(vEndDate), COMPLIANCE_PCT) ), FIRM_NAME, RESP_ROLE_TYPE_CODE, UNDER_SEC_SYM)

My list of bucket categories is a lookup table that is not linked to any other table because the percentage column is dynamic and is recalculated each time the user selects a different start and end date.

Bucket	LowValue	High Value
A	95	99
B	90	94.99
....	....	...

The bucket ranges can change in the future and need this to be dynamic, so using If statements is not a solution for this. I tried the following expression for the Bucket column but it does not work:

= if ( $(vEndDate) >= START_DATE AND $(vEndDate) <= END_DATE,

if( [Compliance %] >= LOW_RANGE AND [Compliance %] <= HIGH_RANGE, BUCKET)

)

Does anybody know how to achieve something like this?

Thanks,

Re: Dynamic Bucket Value Based on Calculated Percentage

Fri, 18 Jul 2014 20:12:52 GMT

Hi Edgar,

This is a very interesting case. Let me ask you a question. How many buckets would you have. You mentioned the bucket ranges can change, but will the number of buckets itself change?

Re: Dynamic Bucket Value Based on Calculated Percentage

Fri, 18 Jul 2014 20:17:05 GMT

Hi Ajay,

The buckets range from A - E. The number of buckets will not change, only the ranges could change in the future.

Thanks,

Edgar

Re: Re: Dynamic Bucket Value Based on Calculated Percentage

Fri, 18 Jul 2014 20:34:22 GMT

Attached is a solution I can think of. This is not the best of solutions but will work since the no. of buckets is only 5 and wont change.

One of the main challenges is that the Bucket table is an island and the resultant dynamic Compliance Value needs to be compared in that island table.

Please take a look at the back-end variables and the expression for the bucket name on the front-end.

Hope its of some help.

Thanks

Re: Dynamic Bucket Value Based on Calculated Percentage

Fri, 18 Jul 2014 20:43:30 GMT

Thank you, Ajay! It's a step in the right direction. Will try this out on Monday.

Thanks again,

edgar