topic Re: RangeMin & RangeMax Cont... in QlikView

RangeMin & RangeMax Cont...

vishsaggi — Fri, 21 Apr 2017 20:03:51 GMT

Hello Sunny,

stalwar1‌

As said here is the new copy. Please do the needful.

Appreciate your time.

Thanks,

Re: RangeMin & RangeMax Cont...

sunny_talwar — Fri, 21 Apr 2017 21:28:12 GMT

Can you explain what is the issue? which object do you need help with? and what exactly are you looking for?

Re: RangeMin & RangeMax Cont...

vishsaggi — Fri, 21 Apr 2017 21:42:39 GMT

My mistake. Wrong qvw file. Apologies for that. Uploaded the latest one to original thread.

In that table we have 3 risk score ratings. When i select a Name from the listbox it should show the rating number.

Like here when i select a name, the number is gone.

Re: RangeMin & RangeMax Cont...

Anil_Babu_Samineni — Sat, 22 Apr 2017 02:49:59 GMT

It's 89 MB. Can you post expression for missing the value?

Re: RangeMin & RangeMax Cont...

sunny_talwar — Sat, 22 Apr 2017 11:48:19 GMT

PFA... I have just modified so that the values show up even after selections are made, but I think that this expression can be improved a lot... seems like you have some unnecessary Aggr() function which might give you bad performance

Re: RangeMin & RangeMax Cont...

vishsaggi — Sat, 22 Apr 2017 14:19:02 GMT

Hey Sunny,

Thanks very much for looking into this. I know the file is too big. That's hitting me back though. ;-). I should have reduced. Now i am facing difficulties downloading the one you attached. Any chances to paste the expression you used.

Re: RangeMin & RangeMax Cont...

sunny_talwar — Sat, 22 Apr 2017 14:20:27 GMT

Second last expression

= RangeMin( Aggr(RangeMax(ONLY({1} Aggr(

CEIl(

(

Num((sum({1}aggr(sum({1<attributedrank ={1}>}individual_disctint_icd_code),person_nbr)) /

sum({1}aggr(count(distinct {1<attributedrank = {1}>}person_nbr),PROVIDER)))

, '##.00')

NUM(Min({1} TOTAL Aggr(RangeMin(ONLY({1} AGGR(

Num((sum({1}aggr(sum({1<attributedrank ={1}>}individual_disctint_icd_code),person_nbr)) /

sum({1}aggr(count(distinct {1<attributedrank = {1}>}person_nbr),PROVIDER)))

,'##.00'),

PROVIDER))), PROVIDER)), '###0')

)

(

Num(Max({1} TOTAL Aggr(RangeMax( ONLY({1} AGGR(

Num((sum({1}aggr(sum({1<attributedrank ={1}>}individual_disctint_icd_code),person_nbr)) /

sum({1}aggr(count(distinct {1<attributedrank = {1}>}person_nbr),PROVIDER)))

,'##.00'),

PROVIDER))), PROVIDER)), '###0')

)

(

Num(Min({1} TOTAL Aggr(RangeMin(ONLY({1} AGGR(

Num((sum({1}aggr(sum({1<attributedrank ={1}>}individual_disctint_icd_code),person_nbr)) /

sum({1}aggr(count(distinct {1<attributedrank = {1}>}person_nbr),PROVIDER)))

,'##.00'),

PROVIDER))), PROVIDER)),'###0')

)

)/10

)

,1)

,PROVIDER))), PROVIDER))

Re: RangeMin & RangeMax Cont...

sunny_talwar — Sat, 22 Apr 2017 14:20:44 GMT

Last expression

= RangeMin( Aggr(RangeMax(ONLY({1} Aggr(

CEIl(

(

Num((sum({1}aggr(sum({1<attributedrank ={1}>}individual_disctint_icd_code),person_nbr)) /

sum({1}aggr(count(distinct {1<attributedrank = {1}>}person_nbr),PROVIDER)))

, '##.00')

NUM(Min({1} TOTAL Aggr(RangeMin(ONLY({1} AGGR(

Num((sum({1}aggr(sum({1<attributedrank ={1}>}individual_disctint_icd_code),person_nbr)) /

sum({1}aggr(count(distinct {1<attributedrank = {1}>}person_nbr),PROVIDER)))

,'##.00'),

PROVIDER))), PROVIDER)), '###0')

)

(

Num(Max({1} TOTAL Aggr(RangeMax( ONLY({1} AGGR(

Num((sum({1}aggr(sum({1<attributedrank ={1}>}individual_disctint_icd_code),person_nbr)) /

sum({1}aggr(count(distinct {1<attributedrank = {1}>}person_nbr),PROVIDER)))

,'##.00'),

PROVIDER))), PROVIDER)), '###0')

)

(

Num(Min({1} TOTAL Aggr(RangeMin(ONLY({1} AGGR(

Num((sum({1}aggr(sum({1<attributedrank ={1}>}individual_disctint_icd_code),person_nbr)) /

sum({1}aggr(count(distinct {1<attributedrank = {1}>}person_nbr),PROVIDER)))

,'##.00'),

PROVIDER))), PROVIDER)),'###0')

)

)/10

)

,1)

,PROVIDER))), PROVIDER))

Re: RangeMin & RangeMax Cont...

vishsaggi — Sat, 22 Apr 2017 14:23:04 GMT

Yes Anil, I am sure it is big, i did it in a hurry. I am trying Sunny expression, so will let you know too.

Thanks

Re: RangeMin & RangeMax Cont...

vishsaggi — Sat, 22 Apr 2017 14:35:13 GMT

Thats superb. Thanks a lot sunny appreciate that. I hope you dont hate me. So sorry for confusing you with the last expression. In the last expression, it is a combination of 3 expressions. I know i am asking too much, you dont have to do it, but if any free time, please do let me know. So in the same file i sent, you have three expressions as shown in the image below. Combination of those 3 expr we have a field named Weighted Chronic Score which has a formula:

= ([% with 1-3 Chronic Diseases] * 0.25) + ([% with 4 - 5 Chronic Diseases] * 0.35) + ([% with >=6 Chronic Diseases] * 0.4)

Can we get this formula straight into our actual Rating Score expr that goes in last column.

Re: RangeMin & RangeMax Cont...

vishsaggi — Sat, 22 Apr 2017 14:40:47 GMT

Very sorry for killing your time in writing up this last expression.

Re: RangeMin & RangeMax Cont...

vishsaggi — Mon, 24 Apr 2017 19:30:47 GMT

Thanks Very much sunny. Appreciate your time and effort. Thanks a lot for your expertise here. I tried the same way using your above expressions for the 3rd Column which i was talking about by combining those 3 expressions to create a final risk score. They seem to be working perfectly.

Many thanks,
V.

Re: RangeMin & RangeMax Cont...

sunny_talwar — Mon, 24 Apr 2017 19:32:46 GMT

Sorry, I never got a chance to come back to this.... But I am glad you were able to figure it out.

Re: RangeMin & RangeMax Cont...

vishsaggi — Mon, 24 Apr 2017 19:47:21 GMT

Oh no, no problem at all. I tried step by step using the other expression you sent. It worked. Thought would try before i raise hands. 🙂