22 Replies Latest reply: Jul 27, 2017 7:03 AM by Sada Siva

# How to get average of Cumilative sum values

Hi Team,

I am having numbers need to be added as cumilatively. Range sum() will do that. After that i need to get average of those numbers monthly. I am having 5 years of data.

For example

In this case if i want to calculate Avg for May-17 i need to consider (72+102)/2 insted of (12+30)/2

 monthyear Value Range sum Jan-17 15 15 Feb-17 20 35 Mar-17 25 60 Apr-17 12 72 May-17 30 102

How can i achieve this. Thanks in advance.

Regards,

• ###### Re: How to get average of Cumilative sum values

like this ?

• ###### Re: How to get average of Cumilative sum values

Hi Arul,

Not exactly,

To calculate average from januay to may- i need to consider only Sum(Range_sum)

I need an expression here, based on the date selection i need to get the average.

example for May-17 average will be (72+102)/2=87

If i select April and May 2017 average =(60+72+102)/3=78 is my desired output.

Regards,

• ###### Re: How to get average of Cumilative sum values

Hi,

You want that current month avg=(last Month rangesum value + Current month rangesum value)/2

Correct?

Thanks,
Neha

• ###### Re: How to get average of Cumilative sum values

Yes neha, based on the months selected by user need to get those month rangesum average

• ###### Re: How to get average of Cumilative sum values

use this-

((Aggr(RangeSum(Above(Sum(Value),1,RowNo(TOTAL))),monthyear))

+

(

If(Dimensionality()=1

,Aggr(RangeSum(Above(Sum(Value),0,RowNo(TOTAL))),monthyear)

,IF(Dimensionality()=0

,Sum(Value)

,RangeSum(Above(Total Sum(Value),0,RowNo(TOTAL))))))

)

/

2

• ###### Re: How to get average of Cumilative sum values

Hi Neha,

It's not working for me. I am not considering only one month here. even for one month also formula is not working.

Will it only work for time dimension or works for all the dimensions?

Regards,

• ###### Re: How to get average of Cumilative sum values

Hi,

It should work for no any selection of month. See the attached file.

You need month to be changes according to your selection.

Thanks ,

Neha

• ###### Re: How to get average of Cumilative sum values

Can you share QVF file. That will help me. I am using sense server.

• ###### Re: How to get average of Cumilative sum values

ohh...& i'm using Qlikview

• ###### Re: How to get average of Cumilative sum values

is this what you want ?

• ###### Re: How to get average of Cumilative sum values

We are almost there, but the thing is i am not seeing those numbers by time dimension, and Time dimension is the filter.

Lets say am selecting year 2017 it will consider all the months from Jan2017 to June2017 and calculate the average of those closing stock numbers.

Regards,

• ###### Re: How to get average of Cumilative sum values

Thanks,

Arvind Patil !

• ###### Re: How to get average of Cumilative sum values

Hi Aravind, can you share QVF of this..

Thanks

• ###### Re: How to get average of Cumilative sum values

wait..

• ###### Re: How to get average of Cumilative sum values

Thanks,

Arvind Patil

• ###### Re: How to get average of Cumilative sum values

Hi Aravind,

monthly average inventory?

This was my actual problem. can you try this?

Regards,

• ###### Re: How to get average of Cumilative sum values

will try

Thanks,

Arvind Patil

• ###### Re: How to get average of Cumilative sum values