Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
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:
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.