topic Re: Quarter average Issue in QlikView

Quarter average Issue

ramyasaiqv — Fri, 08 Sep 2017 16:03:38 GMT

Hi All,

This could be a simple issue, I tried with multiple expressions but it’s still not working. So I’m here for some help please help me.

I’m just trying to calculate the average in quarter but not getting the correct numbers. Not sure what I’m doing wrong please can someone consider attached excel and qvf to resolve my issue.

Issue:

In the attached qvf for example for Q1-2017 the value is 2.75 – this is wrong

Expected result: (10+11+12)/3 = 11

For Q3-2017- 4.58- wrong

Expected: 17.5

Thank you,

Ramya

Re: Quarter average Issue

ramyasaiqv — Fri, 08 Sep 2017 17:09:59 GMT

if you get a chance please can some one help with this. Thanks,

Re: Quarter average Issue

rubenmarin — Fri, 08 Sep 2017 17:29:57 GMT

Hi Rayma, why you say the expected result for Q3 is 17.5? shouldn't it be 18,3333...?

(20+13+22)/3

You can try with this expression:

Avg(Aggr(Sum([Data]), Year, Month, [Level Data]))

First sums the data of each Month, the does the average of those values.

Re: Quarter average Issue

ramyasaiqv — Fri, 08 Sep 2017 18:06:35 GMT

Hi Ruben,

Sorry it was my bad, result for Q3 is 17.5? shouldn't it be 18,3333...? yes this is correct. it worked .Thank you,

Regards,

ramya

Re: Quarter average Issue

ramyasaiqv — Fri, 08 Sep 2017 19:08:58 GMT

Hi Ruben,

I'm sorry actually for current month there will be no data. So for current quarter (Q3-2017) the calculation should include (july+august)/2.

I have attached the new excel and qvf, i wrote a set expression to exclude '0's but it's not working please can you help. Thank you so much

Ramya

Re: Quarter average Issue

rubenmarin — Sun, 10 Sep 2017 10:03:21 GMT

Hi Ramya, the sum expresion needs the field to sum:

Avg(Aggr(Sum({<[Data]={'>0'}>} Data), Year, Month, [Level Data]))

But I'm not sure if this will be ok, in the previous months there are values as zero, if these zero's needs to be counted in the avg you can't ignore zeros.

Another option can be avoid to load from excel recods without any value, to do this you can add this condition to the where clause:

Where YEAR(trim([Date]))& Num( Month(date(trim([Date]), 'MMMM')), '00')<= text(date(today(),'YYYYMM'))and [Level Data] <> 'All'

and Len(Trim(Data))>0 // if there is no value, it won't be counted for average

;

Re: Quarter average Issue

ramyasaiqv — Sun, 10 Sep 2017 23:38:39 GMT

Hi Ruben,

Thank you, i tried the second method it worked.

Ramys