Average calculation

[ramyasaiqv]

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

[Anil_Babu_Samineni]

PFA

[ramyasaiqv]

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

[sunny_talwar · Accepted solution]

May be this

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

[ramyasaiqv]

Thank you Sunny.

Ramya