topic Re: Formula as dimension in QlikView

Formula as dimension

arpitkharkia — Fri, 04 Aug 2017 08:44:15 GMT

I have the following expression in the front end:

count({<STATUS_CD=13>}OP)/count(CLAIM_NO)

I need the output of the above expression to be bucketized into 5 buckets of 20% each. Then i have to use that dimension in a chart. I tried using a variable but not able to apply.

Help appreciated!

Re: Formula as dimension

Anil_Babu_Samineni — Fri, 04 Aug 2017 08:53:58 GMT

Your expression this?

count({<STATUS_CD={13}>} OP)/count(CLAIM_NO)

Note - How you want to show bucket. Can you explain little more

Re: Formula as dimension

tresB — Fri, 04 Aug 2017 08:54:55 GMT

May be like:

Class(Aggr(count({<STATUS_CD={13}>}OP)/count(CLAIM_NO), YourDimension), 0.2)

Re: Formula as dimension

arpitkharkia — Fri, 04 Aug 2017 09:01:08 GMT

so this is formula to calculate HR. The result will be in percentage so i want to have buckets of percent and show how many case falls in each of the bucket. so how many cases are there for my HR in 0-20% and so on.

Re: Formula as dimension

arpitkharkia — Fri, 04 Aug 2017 09:02:10 GMT

You want me to this in the front end as calculated dimension or through resident load in the script?

Re: Formula as dimension

tresB — Fri, 04 Aug 2017 09:03:54 GMT

Front-end?

Re: Formula as dimension

arpitkharkia — Fri, 04 Aug 2017 09:04:55 GMT

Sorry if there is any confusion. So i have to use it in the script right?

Re: Formula as dimension

tresB — Fri, 04 Aug 2017 09:09:50 GMT

You CAN do it in front-end itself.

Re: Formula as dimension

arpitkharkia — Fri, 04 Aug 2017 09:44:02 GMT

its working just fine. Thanks a lot. One more thing i want to label the different classes accordingly. How can i do that?

Re: Formula as dimension

tresB — Fri, 04 Aug 2017 09:52:45 GMT

You can probably do so using string manipulation functions. Try to share a sample explaining your expected output, may be I could help better.

Re: Formula as dimension

arpitkharkia — Fri, 04 Aug 2017 10:01:09 GMT


0 <= x <0.2
0.2 <= x <0.4
0.4 <= x <0.6
0.6 <= x <0.8
0.8 <= x <1
1 <= x <1.2

I got above from the class definition.

i want the following:


0-20%
20-40%
40-60%
60-80%
80%-100%

I want the last 2 to be clubbed to 80-100%. Is it possible? I tried using if while creating dimension.

Re: Formula as dimension

tresB — Fri, 04 Aug 2017 10:12:15 GMT

Since this class number is limited, you could try like:

vExpression=Aggr(count({<STATUS_CD={13}>}OP)/count(CLAIM_NO), YourDimension) // '=' symbol might not be required before variable definition

If( (vExpression)>0.8, '>80% and above' ,

If( (vExpression)>0.6, '>60-80%' ,

If ( (vExpression)>0.4, '>40-20%',

if( (vExpression)>0.2, '>20-40%', '<=20%' ))))

Re: Formula as dimension

arpitkharkia — Mon, 07 Aug 2017 08:23:06 GMT

Hey Tresesco

Can i use this calculated dimension for some actions to be assigned to text boxes?

Re: Formula as dimension

tresB — Mon, 07 Aug 2017 09:14:45 GMT

Check my reply here: Selection of calculated dimension