Above Function

[tellmalaika17]

We are using above function to create a column for moving average for 3 months. Have dimension as quarter and measure as avg(sales). For example

Quarter  Sales

Q1 14      100

Q2 14      50

Q3 14      125

want to create 3rd column as below :

Quarter  Sales  3 months average

Q1 14      100    

Q2 14      50           100

Q3 14      125         50

With above function this works fine, but it breaks when i click on the quarter filter. If i select q2 14, q3 14 it should show values as 100 and 50 but Q2 14 shows null and Q3 14 as 100 since it using above function and making it null.

On selection the numbers should remain as is for 3 months average. I tried to ignore Quarter selection but it does not work. Any other alternative method to achieve this.

Appreciate your help

[kaushiknsolanki]

Hi,

If you are using the latest version of Qlik Sense, then you should check this inbuilt option to create moving avg.

https://www.youtube.com/watch?v=Q1FkCSOHvno

[tellmalaika17]

Thanks this is helpful information but do not work for me since i am not using Feb version. However even if i try using the rangeavg logic it doesnt work. Avg for the logic has been derived in the backend. So all i do in the frontend is only(sales)

[Saravanan_Desingh]

One solution in Script.

tab1:
LOAD *, Peek(Sales) As [3 months average];
LOAD * INLINE [
    Quarter, Sales
    Q1 14, 100
    Q2 14, 50
    Q3 14, 125
];

Output:

[tellmalaika17]

Thank you for your response. If I do Peek(Sales) as 3 months then instead of giving Q114 value 100 in Q214 it is just removing Q114 and showing Q214 value 50 in Q114. So it is not showing historic value. I need to see in Q114 value ie 100 in Q214. which will show me historic average value.