Show rolling 3 periods of periodic average in a Straight Table

[#1]

Here's a simple data table with sales amount for two stores over 13 periods.

Period	Store1	Store2
P01	50	45
P02	40	65
P03	30	25
P04	54	15
P05	32	82
P06	68	32
P07	14	42
P08	8	35
P09	0	21
P10	9	53
P11	87	64
P12	25	87
P13	35	25

I need to figure out moving average of prior 3 periods for each period regardless the selection. Normally (in excel) I would calculate the average sales by period, next I would sum it up to get the rolling 3 periods. The result should be the last cell in this table (with P1 and P2 being incomplete). P4 Rolling 3Periods = $114.5 which is a sum of averages for P4, 3, and 2.

Period	Store1	Store2	Avg	Rolling3
P01	50.0	45.0	47.5	47.5
P02	40.0	65.0	52.5	100.0
P03	30.0	25.0	27.5	127.5
P04	54.0	15.0	34.5	114.5
P05	32.0	82.0	57.0	119.0
P06	68.0	32.0	50.0	141.5
P07	14.0	42.0	28.0	135.0
P08	8.0	35.0	21.5	99.5
P09	-	21.0	21.0	70.5
P10	9.0	53.0	31.0	73.5
P11	87.0	64.0	75.5	127.5
P12	25.0	87.0	56.0	162.5
P13	35.0	25.0	30.0	161.5

I've tried aggregation formula in combination with rangesum, but it just wouldn't seem to be able to disregard the current selections. Then I've also tried to create AsOf Table but the problem with this (I think) is that it wouldn't let me calculate the period average first and then do the sum.

I'm just not sure how to even think about the logic in the expression, so I'd appreciate anyone's input. As a side note, I do have a numeric id for each period in a separate table, which I also tried to use.

Thanks.

[#2]

Hi Elena,

Did you try it like this?:

rangesum(above(sum(Avg),0,3)) // Avg here is the name of the expression before

You can substitute Sum(Avg) with the expression you have in Avg column as well.

Hope this helps.

Kind regards,

Nuno

[#3]

Hello Nuno.

Yes, I have tried that but it doesn't seem to be able to disregard the current selection. For example, if I select P4, it won't calculate outside of the table values. Which I've also tried to do by inserting a modifier {<Period=>} to disregard the selection.

Any ideas?

[#4]

I don't think that is possible without doing it on the script like this example:

http://community.qlik.com/thread/48195

[#5]

Okay. Seems a bit complex for a beginner like me. I will definitely play around with this suggestion, thank you. Will post the results as soon as.

[#6]

Update: unfortunately the above suggestion doesn't work because in the referenced link, the file that is used to test, wouldn't be able to do what I want to show if I selected more than 1 product. It shows the rolling average for each product but not for each month.

[jagan]

Hi,

Please find attached file for solution, hope it helps you.

Regards,

Jagan.

[#8]

Thank you Jagan. This is pointing me in the right direction. I will play around with this because I have a lot more data rows (hundreds of stores and rolling 13Periods).

[Gysbert_Wassenaar]

It's not clear to me what your data model is. You mention hundreds of stores. Does that mean you have hundreds of columns? If so I think you should use the crosstable function to transform the data to a more usable form. If not you can first create a summary table to calculate the averages per period. You can then use create an AsOf table and link it to that summary table.

The summary table would be something like:

AveragesPerPeriod:

load Period, avg(Amount) as Average

resident ...facttable...

group by Period;

[#10]

Okay, my bad for not making myself clear. My data model is quite complex so I tried to reduce it to a sample but failed to show it properly.

All of our fields are in rows and only value is in one column (if that makes sense). Here's a sample of a data model. Imagine it expanded to hundreds of stores and periods.

Entity	City	Period	Account	Category	Value
Store1	Abc	P01	Sales	Actual	354
Store1	Abc	P02	Sales	Actual	845
Store1	Abc	P01	Sales	Budget	354
Store1	Abc	P02	Sales	Budget	687
Store2	Abc	P01	Sales	Actual	854
Store2	Abc	P02	Sales	Actual	654
Store2	Abc	P01	Sales	Budget	321
Store2	Abc	P02	Sales	Budget	855
Store1	Abc	P01	Labor	Actual	211
Store1	Abc	P02	Labor	Actual	322

So storing averages in a separate table - would that make sense to do?