RangeMin Or RangeMax Excel MinMax in Qlikview

[vishsaggi]

Hello Experts,

I have an excel sheet and QVW attached. I am trying to get the Excel formula converted into QV.

Please advise accordingly.

Formula is in the last column.

Please let me know if any further details needed.

stalwar1‌nicole_kowalsky‌vinieme12‌tresesco‌swuehl‌

Thanks,
V.

[Nicole-Smith]

This expression works:

=RANGEMIN(RANGEMAX(CEIL((ScoreCalc - Min({1}TOTAL ScoreCalc)) / ((Max({1} TOTAL ScoreCalc) - Min({1}TOTAL ScoreCalc)) / 10)), 1), 10)

I've also attached your QVW file with the working expression.

[Kushal_Chawda]

script solution could be like below

Data:

LOAD Name,

    Score,

    ScoreCalc

FROM

[MinMaxQV.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(Data)

LOAD min(ScoreCalc) as MinScore,

          max(ScoreCalc) as MaxScore

Resident Data;

Final:

NoConcatenate

LOAD *,

        rangemin(rangemax(Ceil ((ScoreCalc-MinScore)/((MaxScore-MinScore)/10)),1),10) as ScoreRating

Resident Data;

DROP Table Data;

[vishsaggi]

Thanks Nicole, appreciate your time, will check and get back if any questions.

[vishsaggi]

Thanks Kushal, this looks interesting doing it in Script, but my StoreCalc is a calculated field, so doing this in Script might be more complex. Appreciate your help too. Will look into this as well.

[vishsaggi]

Hello Nicole,

May be my shortsightedness here, forgot to mention that my StoreCalc is a calculated field with this expression, so i implemented that expression you mentioned but i am getting 10 in all the rows where that formula is used.

This is my actual expression. Any tweaks i have to do here, please suggest:

= RANGEMIN( RANGEMAX(

  CEIL(

   (

     Num(Sum(Aggr(count(DISTINCT(if(attributedrank =1,pnbr))) * [frequesncy], PROVIDER, Age, sex)) , '###0')

     -

     MIN({1} TOTAL Num(Sum(Aggr( count(DISTINCT(if(attribute =1,pnbr))) * [frequesncy], PROVIDER, Age,sex))), '###0')

   )

   /

   (

     NUM(

     (

   (

       MAX({1} TOTAL Num(Sum(Aggr( count(DISTINCT(if(attribute =1,pnbr))) * [frequesncy], PROVIDER, Age,sex))), '###0')

   ) -

   (

       MIN({1} TOTAL Num(Sum(Aggr( count(DISTINCT(if(attribute =1,pnbr))) * [frequesncy], PROVIDER, Age,sex))), '###0')

   )

     )/10

     , '###0')

    )

   ,1)

),10)

Apologies for that.

[sunny_talwar]

Don't know much, but you would need another Aggr() here

[vishsaggi]

Hello Sunny,

I tried adding Aggr there but no good. I am creating a working copy and will upload soon.

Please look into this when you get time.

Thanks,

V.

[vishsaggi]

Hello Nicole, Sunny,

Here is the workingcopy of it. Any suggestions much appreciated.

I want to disregard the selections and get the value static in the last column..

Many thanks for you time.

V.

[Nicole-Smith]

I added this to the load script:

RISK_Score:
LOAD PROVIDER,
  sum([CMS RISK Score]) AS [CMS RISK Score]
GROUP BY PROVIDER;
LOAD PROVIDER,
  Age,
  sex,
  count(DISTINCT(if(attributedrank =1,person_nbr))) * [CMS frequesncy] AS [CMS RISK Score]
RESIDENT CH196_20170418_164837
GROUP BY PROVIDER, Age, sex, [CMS frequesncy];

LEFT JOIN (RISK_Score)
LOAD min([CMS RISK Score]) AS [Min CMS RISK Score],
  max([CMS RISK Score]) AS [Max CMS RISK Score]
RESIDENT RISK_Score;

Then I updated the expressions to be:

CMS Risk Score:

Sum([CMS RISK Score])

CMS Risk Score Rating:

=RANGEMIN(RANGEMAX(CEIL(([CMS RISK Score] - [Min CMS RISK Score]) / (([Max CMS RISK Score] - [Min CMS RISK Score]) / 10)), 1), 10)

I've attached your file back with these changes implemented.