topic Re: Average calculation in QlikView

Average calculation

ramyasaiqv — Fri, 01 Sep 2017 05:08:24 GMT

Hi All,

I’m just trying to calculate the average of the Items across the months and having issue when there is no data (N/A) .

The issue is as seen in the attached excel and qvf when there is no data (either 0, -, N/A) my expression is not calculating the average correctly.

For example: in the attached excel there are 5 items and for Jan-17 item ‘XLS’ doesn’t have data (the value is N/A) . The expression I wrote is calculating the average as 2.64 which was supposed to be 3.3. This is happening because instead of 4 items my expression is considering 5 items (even though Item XLS has N/A). Please someone help me to resolve this issue. Thank you,

Ramya

Re: Average calculation

Anil_Babu_Samineni — Fri, 01 Sep 2017 05:58:49 GMT

PFA

Re: Average calculation

ramyasaiqv — Fri, 01 Sep 2017 16:46:54 GMT

Hi Anil,

Thank you so much for the response it worked for one of the expressions. I have one more expression which is bit complicated and don’t know how to use avg over sum function. Please can you help me with this?

It’s the same data the bottom chart is across months and the top chart is across last month of every quarter (except current quarter)

I have attached the qvf for reference.

Thank you,

Ramya

Re: Average calculation

sunny_talwar — Fri, 01 Sep 2017 17:17:52 GMT

May be this

Avg(If(YYYYMM = Max(TOTAL {<Data -= {'N/A'}>} YYYYMM), Data, If(mod(MonthNum,3) = 0 , Data)))

Re: Average calculation

ramyasaiqv — Fri, 01 Sep 2017 23:06:26 GMT

Thank you Sunny.

Ramya