topic Performing Calculation for Current and Past Month Information in QlikView

Performing Calculation for Current and Past Month Information

Tue, 24 Jun 2014 10:18:17 GMT

Hi All,

I am have a straight table displaying different months of data as below and am unsure how do I get average of 2 months.

MonthYear	Item	Total Cost	AverageOf2Months
01/2014	A	100	Expected Value 100+300/2
02/2014	A	300	Expected Value 300+(Till Date Total Cost)/2
01/2014	B	200	Expected Value 200+400/2
02/2014	B	400	Expected Value 400+(Till Date Total Cost)/2

Anyone knows how can I perform the calculation to get my expected value?

I have the full date e.g 31/01/2014 but I have converted it to months/year to perform necessary calculation to total the cost of item A for the month. I am unsure how would I reference the total cost of item A in Feb 2014 to get my average. Can anyone help?

Thanks!

Re: Performing Calculation for Current and Past Month Information

Mon, 30 Jun 2014 01:25:34 GMT

Anyone able to help? Thanks!

Re: Performing Calculation for Current and Past Month Information

Mon, 30 Jun 2014 01:43:29 GMT

can you do a

rangeavg(Total Cost, Below(Total Cost))

Re: Re: Performing Calculation for Current and Past Month Information

Mon, 30 Jun 2014 01:48:34 GMT

PFA an example.

Re: Re: Performing Calculation for Current and Past Month Information

Mon, 30 Jun 2014 02:05:34 GMT

Hi Ramkumar,

The solution works but is there anyway I could get the formula to calculate by item?

Base on the sample qvw, I have to sort the table by item before it works and is there anyway I can get the last value to not perform average if it is the last value?

Regards,
Raymond

Re: Re: Re: Performing Calculation for Current and Past Month Information

Mon, 30 Jun 2014 02:14:52 GMT

PFA.

Already the last item was not calculating average it it was the last item. May be i don't get you.