Rolling 30-days Average

[Anonymous]

Hi guys,

I want to calculate Rolling 30-days average using script (not by using expressions in the front end).

Because I need to create a flag by comparing the normal average and Rolling 30-days average.

(If Normal avg >= Rolling 30-days avg then 0 otherwise 1) - Flag

So, I need to achieve these requirements in script itself. Am I right? Please help me out.

Attached the sample data in excel and qvw below. I need to find rolling 30-days file size average.

Thanks,

Siva.

[Kushal_Chawda]

if for a particular day  I just have 15 days, so you want the Avg of 15 Days?

[Anonymous]

Thanks for the reply, Kushal.

Yes, for a particular day I need it for 30 days.

[Kushal_Chawda]

Data:

LOAD Date,

    [File Name],

    [File Size]

FROM

[Sample2.xlsx]

(ooxml, embedded labels, table is Sheet1);

join (Data)

LOAD Date as Date1

Resident Data;

New:

LOAD *,

    if(Flag=1 and Previous(Flag)=0,[File Size],if(Previous(Flag)=1 and Flag<>0,rangesum([File Size],Peek('AccumFileSize')))) as AccumFileSize;

LOAD *,

    if(Date1-Date<=30 and Date1-Date>=0,1,0) as Flag

Resident Data

Order by Date1,Date;

DROP Table Data;

Left Join(New)

LOAD Sum(Flag) as DateCount,

    Date1

Resident New

Group by Date1;

Final:

NoConcatenate

LOAD *,

    round(Alt(AccumFileSize,[File Size])/DateCount) as Rolling30AvgSize

Resident New

where Date1=Date;

DROP Table New;

see the attached file

[Anonymous]

Thanks, Kushal. But if I have file size for the same file name on different file dates, how can we manage it?

[Kushal_Chawda]

So do you want file Name wise rolling 30 Days ?

[Anonymous]

Sorry Kushal, I forgot to mention that there is file type. A file name has two file types. So I need to include that too.

Each file name can have two file size based on the file type.

[Kushal_Chawda]

Then the sample data is not enough to build that logic. Please share more sample data where we have Same Filenames on different date

[Anonymous]

Thanks, please find sample data for that logic.

[Kushal_Chawda · Accepted solution]

see the attached