Avg value issue

[ravindraa]

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

[MK_QSL · Accepted solution]

May be this

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

or

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

Replace

Sales with sales

Date with cal_day

[ravindraa]

Thank you very much for replay  Manish Kachhia,

[MayilVahanan]

Hi

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)