Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
vishsaggi
Champion III
Champion III

RangeMin & RangeMax Cont...

Hello Sunny,

stalwar1

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

Appreciate your time.

Thanks,

V.

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

13 Replies
sunny_talwar

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

vishsaggi
Champion III
Champion III
Author

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.

Capture.PNG

Anil_Babu_Samineni

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
sunny_talwar

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

vishsaggi
Champion III
Champion III
Author

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.


V.

sunny_talwar

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

sunny_talwar

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

vishsaggi
Champion III
Champion III
Author

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

V.

vishsaggi
Champion III
Champion III
Author

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:

Capture.PNG

= ([% 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.