topic Above Function in App Development

Above Function

tellmalaika17 — Wed, 22 Dec 2021 16:36:32 GMT

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

Re: Above Function

kaushiknsolanki — Wed, 01 Jul 2020 19:12:29 GMT

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

Re: Above Function

tellmalaika17 — Wed, 01 Jul 2020 19:25:49 GMT

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)

Re: Above Function

Saravanan_Desingh — Thu, 02 Jul 2020 03:21:40 GMT

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:

Re: Above Function

tellmalaika17 — Mon, 06 Jul 2020 14:49:14 GMT

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.