Sum of Running Average!

[nevilledhamsiri]

Hi Vineeth,

Table below carries three expressions. 1st one is for  the total Premium Month wise. 2nd one is for Running total of which total sum is correct (Eg for CC022, IT IS 1688629 which i the cumulative in March. 3rd one is for running average. But here the total sum needs to be the running average for March. Eg for CC022, The total of running  average  requires to be Rs 562876/= but its is Rs 1688629. How it is made to read as Rs 562564.

Used expression for three expressions are as follows!

sum(PREMIUM)

RangeSum (above(sum(PREMIUM),1,RowNo()-1))+sum(PREMIUM)

rangeavg(above(sum(PREMIUM),0,3))

How i will  rework the third expression for the total of average to take March average

CC022	EM00	CC022	Jan	445661	445661	445661
			Feb	585467	1031128	515564
			Mar	657500	1688629	562876
		Total		1688629	1688629	1688629
	Total			1688629	1688629	1688629
CC024	EM00	CC024	Jan	705622	705622	705622
			Feb	694869	1400492	700246
			Mar	982507	2382998	794333
		Total		2382998	2382998	2382998
	Total			2382998	2382998	2382998
CC096	EM00	CC096	Jan	288252	288252	288252
			Feb	229816	518068	259034
			Mar	255148	773216	257739
		Total		773216	773216	773216
	Total			773216	773216	773216

Many tanks

Neville

[sunny_talwar]

May be this

If(Dimensionality() = 4,

RangeAvg(Above(Sum(PREMIUM), 0, 3)),

FirstSortedValue(TOTAL <BRANCH_CODE, COUNTER_CODE>Aggr(RangeAvg(Above(Sum(PREMIUM), 0, 3)), BRANCH_CODE, COUNTER_CODE, (TR_MONTH,(NUMERIC))), -Aggr(TR_MONTH, BRANCH_CODE, COUNTER_CODE, (TR_MONTH,(NUMERIC)))))

But what would the final total be?

[nevilledhamsiri]

Hi Sunny,

Happy to see you back. Ohh, That formula is too much for me. I do remember once you brought the same results (Latest Month average) to the total column through a less complicated & short expression. This is  alright but very hard to keep it in mind.Any how thanks for your answer. You look like the man in the hour. Very happy to see your thorough knowledge.

If  possible please  produce me a short formula . For the 2nd issue you raise, if it is the average of all three counters, I think ,it would be rather fine.But if you propose any other answer for that, I welcome that very much.

Also Sunny, I wish to know how you circle the specific point in the table to highlight it. I look forward the steps to be followed that too.

Look forward for your assistance

Many Thanks

[sunny_talwar]

I don't think there is an easy way to do this... even if I try, it would be another difficult expression....

for the final total, you want to see the (562876 + 794333 + 257739)/3?

[nevilledhamsiri]

Of course Yes.

Also please show me the steps on how  you  circle the thing in the table.

I may also seek your advice, as to how one may develop such an ability to build up a formula. Please pass me your experience because I am a beginner & really interested in studding this.

Thanks a lot

[sunny_talwar]

Also please show me the steps on how  you  circle the thing in the table.

Circle the thing? Not sure I understand what you mean my friend?

[sunny_talwar · Accepted solution]

Try this

If(Dimensionality() = 4,

RangeAvg(Above(Sum(PREMIUM), 0, 3)),

Avg(Aggr(FirstSortedValue(TOTAL <BRANCH_CODE, COUNTER_CODE > Aggr(RangeAvg(Above(Sum(PREMIUM), 0, 3)), BRANCH_CODE, COUNTER_CODE, (TR_MONTH,(NUMERIC))), -Aggr(TR_MONTH, BRANCH_CODE, COUNTER_CODE, (TR_MONTH,(NUMERIC)))), BRANCH_CODE, COUNTER_CODE)))

[nevilledhamsiri]

Sunny,

I need to mark your answer as ''Correct" but once I need to see the data in my  Qlik point table, I cannot access the same dialog as my point table runs halfway through & stops there. Sooner this issue is restored, your answer will be marked as ''Correct"

Many Thanks