Date Expression

[nate_eyster]

Stuck on an expression. Use case below.

ex: suppose if I select month July'16 in list box it should give me average by taking the previous two months and the present month selected as well. I want this to be dynamic across all months. I want to achieve this in Qlikview as a table and also as line graph.

Average of Category tickets count = (present month + past 2 months)/3. If you go to cell 'K11' for 2nd table  you will be able to understand what I am trying to explain, if I am not clear. 

Example as per Excel sheet:

July'16 Average ticket count: = (I3+J3+K3)/3 = 21

I tried with set expressions and other workarounds but failed to achieve it.

[sunny_talwar]

In a pivot table, you can try this

RangeAvg(Before(Sum(Value), 0, 3))

[nate_eyster]

Still can't wrap my head around this.. rearranged data in this format..

Category tickets count	MonthYear	Tickets
Simple	Jan'16	32
Medium	Jan'16	22
Complex	Jan'16	21
Simple	Feb'16	25
Medium	Feb'16	24
Complex	Feb'16	20

[sunny_talwar]

Not sure what you mean, did the above expression not work for you in a pivot table?

[sunny_talwar · Accepted solution]

Like this?

=If(ColumnNo() > 2, RangeAvg(Before(Sum({<MonthYear>}Tickets), 0, 3))) * Avg(1)

[nate_eyster]

Bingo, thanks Sunny!