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.

[sunny_talwar · Accepted solution]

Again I am not 100% sure what you want, but try this guy in the front end

= RANGEMIN( Only({1} AGGR( RANGEMAX( Only({1}AGGR(

  CEIL(

  (

    Num(Sum({1}Aggr(count({1}DISTINCT(if(attributedrank =1,person_nbr))) * Only({1}[CMS frequesncy]), PROVIDER, Age, sex)) , '###0')

    -

    Num(MIN({1}TOTAL Aggr(RANGEMIN(Sum({1}Aggr(DISTINCT count({1}DISTINCT(if(attributedrank =1,person_nbr))) * Only({1}[CMS frequesncy]), PROVIDER, Age,sex))), PROVIDER)), '###0')

  )

  /

  (

    NUM(

    (

  (

      Num(MAX({1}TOTAL Aggr(RANGEMAX(Sum({1}Aggr(DISTINCT count({1}DISTINCT(if(attributedrank =1,person_nbr))) * Only({1}[CMS frequesncy]), PROVIDER, Age,sex))), PROVIDER)), '###0')

  ) -

  (

      Num(MIN({1}TOTAL Aggr(RANGEMIN(Sum({1}Aggr(DISTINCT count({1}DISTINCT(if(attributedrank =1,person_nbr))) * Only({1}[CMS frequesncy]), PROVIDER, Age,sex))), PROVIDER)), '###0')

  )

    )/10

    , '###0')

    )

  ,1)

, PROVIDER))), PROVIDER)))

[vishsaggi]

Thanks Nicole, i will look into this. As the following fields mentioned in my WorkingCopy is coming from big SQL code, i am trying to implement the script logic if that fits accordingly. Will get back on this. Appreciate your time.

[vishsaggi]

Thanks Sunny, I knew Only should be used but its all playing around and I failed to use it. However, Appreciate your help with this and with your script it does not lower down the table when i select specific Name. I mean i am seeing like below:

Not reducing the table based on selections. Then i remove Only({1} ....) from the First RANGEMIN() and it works like a charm. Like Below:

Appreciate your time and thanks very much for your help.

[sunny_talwar]

I was not 100% certain as to what was needed... but ya Removing {1} from outer aggregation should do it... Also multiplying with Avg(1) should do it also

[vishsaggi]

Thanks ever so much every one for being patience and helpful. I know the logic writing at script level is good, but due to time constraints and complexity i have to stick at front end and used Sunny's expression which works good. But i am sure Nicole's and Kushals script logic will definitely work.

Thanks everyone again. Especially Sunny and Nicole as always, I get quick help in short notice. I remember the times you guys helped me when I was in head scratching situations. Appreciate that.

Cheers,

V.

[vishsaggi]

stalwar1, you have a little time. I am stuck with the same requirement but with another Expression? Let me know.

[sunny_talwar]

Sure, what's up brother?

[vishsaggi]

Thanks very much.

I will create a new ticket and upload a WorkingCopy. Does that sound Ok?

[sunny_talwar]

Yup

[vishsaggi]

Hello Sunny,

Here is the new thread.

RangeMin & RangeMax Cont...

Thanks,

V.