topic Re: Avg value issue in QlikView

Avg value issue

ravindraa — Wed, 10 Feb 2016 10:57:28 GMT

Hi All,

I have struck one place please give me some suggestion to come out from that .

My requirement is, i have one date field and sales filed we need to display the Days count.

To display the days count i have one requirement that first i have to find out sales as per the month wise then i have to find out how many days has more sales compare to avg sales.

EX:

for 1st day sales 10, 2nd day sales 20 ,3rd day sales 30 ..................16th day sales 160, 17th day sales 170,...................30th daysales 300.

if our avg sales is 160 how many days is greater than 160 like 17th day to 30th day .

for that i wrote expression like

if(aggr(avg(sales),cal_Month)>=aggr(sales,cal_Month,cal_day),count(cal_day))

Please give me any suggestion .

Thanks in advance

Re: Avg value issue

MK_QSL — Wed, 10 Feb 2016 11:08:24 GMT

May be this

=SUM(Aggr(IF(SUM(Sales) >= Avg(TOTAL Sales), 1,0),Date))

=SUM(Aggr(IF(SUM(Sales) >= Avg({1}TOTAL Sales), 1,0),Date))

Replace

Sales with sales

Date with cal_day

Re: Avg value issue

ravindraa — Wed, 10 Feb 2016 11:12:06 GMT

Thank you very much for replay Manish Kachhia,

Re: Avg value issue

MayilVahanan — Wed, 10 Feb 2016 11:12:30 GMT

Try like this in script

Temp:

Load * from yourTableName;

Left Join(Temp)

Load cal_Month, Avg(Sales) as AverageSales Resident Temp;

Final:

Load *, If(Sales > AverageSales, 1, 0) AS Flag resident Temp;

Drop table Temp;

Then ,

Dim: Cal_Month

In expression

Count({<Flag = {1}>}cal_day)