topic Re: Range Sum in QlikView

Range Sum

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have the data like this.

TurnOver= Readmission/admissions

July TurnOver= Readmission/admissions

August TurnOver= July Turnover + August TurnOver / 2

Sept Turnover = July Turnover + August TurnOver +Sept Turnover / 3

This is how the calculation should be. If I'm selection only July it should take only 1month turnover and If making selection as August then it should take Avg of July and August Turnover.

Please help. Urgent requirement. stalwar1‌ mto‌jagan‌

Fiscal Month	Gender	% Turnover	Readmission	Admissions
Jul	Female	1.3%	184	14452
Jul	Male	1.0%	207	20262
Aug	Female	1.0%	142	14553
Aug	Male	1.0%	206	20374

Re: Avg Calculation

Anonymous — Thu, 22 Sep 2016 18:35:14 GMT

Dimension: Month

Expression:

RangeSum( Above( sum( Readmission) /sum(admissions) ,0,rowno()))/rowno()

Re: Range Sum

Anonymous — Thu, 22 Sep 2016 18:55:26 GMT

Out put would be:

Jul	Female	1.3%
Jul	Male	1.0%
Aug	Female	1.2%
Aug	Male	1.0%

Re: Range Sum

Anonymous — Thu, 22 Sep 2016 18:59:05 GMT

akka.. What is row () here...? Rajitha here

Re: Range Sum

sunny_talwar — Thu, 22 Sep 2016 19:04:52 GMT

I am getting this

Expression:

=Aggr(RangeAvg(Above(Sum(Readmission)/Sum(Admissions), 0, RowNo())), Gender, [Fiscal Month])

Re: Range Sum

Anonymous — Thu, 22 Sep 2016 19:36:53 GMT

Bro, I have one calculated Dimension for Gender =IF(GENDER='Unknown',Null(),GENDER)

My actual expression is=

(sum({<GENDER_DETAIL-={'Elected*'}>}VOL_TO_INDICATOR)

(count({<GENDER_DETAIL-={'Elected*'}>}FTE)))

Please correct me If I'm wrong, Final expression which I changes to is :

=Aggr(RangeAvg(Above(sum({<GENDER_DETAIL-={'Elected*'}>}VOL_TO_INDICATOR)

(count({<GENDER_DETAIL-={'Elected*'}>}FTE)), 0, RowNo())),

IF(GENDER='Unknown',Null(),GENDER), [Reporting Month])

Please advise, how to write it. The above one is not working for me according to ur logic.

stalwar1‌

Re: Range Sum

sunny_talwar — Thu, 22 Sep 2016 19:38:43 GMT

Aggr cannot take in calculated dimension, have you tried with just this?

=Aggr(RangeAvg(Above(sum({<GENDER_DETAIL-={'Elected*'}>}VOL_TO_INDICATOR)

(count({<GENDER_DETAIL-={'Elected*'}>}FTE)), 0, RowNo())), GENDER, [Reporting Month])

Re: Range Sum

Anonymous — Thu, 22 Sep 2016 19:47:47 GMT

The august Female turnover is showing 1.0% instead of 1.1%

Re: Range Sum

sunny_talwar — Thu, 22 Sep 2016 19:50:16 GMT

It would be difficult to say anything unless I see. Also, you are just comparing upto 1 decimal? Have you looked at the difference when we have lets say 4 decimals? May be the difference is rounding.

You are getting 1.051 and the solution might be giving 1.049.

Re: Range Sum

rahulgurram — Fri, 23 Sep 2016 16:56:59 GMT

Hi Vinay,

Try this if you are still working on this issue

(rangesum((sum({<GENDER_DETAIL-={'Elected*'}, GENDER = {'Female'}>}ReAdmissions)/(count({<GENDER_DETAIL-={'Elected*'}, GENDER = {'Female'}>}FTE))),Above(Female)))

/(rangesum(above((count(Distinct {<GENDER_DETAIL-={'Elected*'}>}FISCAL_MONTH))))+1)

Thanks