topic Re: Rolling 30-days Average in QlikView

Rolling 30-days Average

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Rolling 30-days Average

Kushal_Chawda — Wed, 11 Oct 2017 11:00:11 GMT

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

Re: Rolling 30-days Average

Anonymous — Wed, 11 Oct 2017 11:13:14 GMT

Thanks for the reply, Kushal.

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

Re: Rolling 30-days Average

Kushal_Chawda — Wed, 11 Oct 2017 11:31:36 GMT

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

Re: Rolling 30-days Average

Anonymous — Wed, 11 Oct 2017 12:35:26 GMT

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

Re: Rolling 30-days Average

Kushal_Chawda — Wed, 11 Oct 2017 12:45:12 GMT

So do you want file Name wise rolling 30 Days ?

Re: Rolling 30-days Average

Anonymous — Wed, 11 Oct 2017 12:53:59 GMT

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.

Re: Rolling 30-days Average

Kushal_Chawda — Wed, 11 Oct 2017 12:57:32 GMT

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

Re: Rolling 30-days Average

Anonymous — Wed, 11 Oct 2017 13:07:27 GMT

Thanks, please find sample data for that logic.

Re: Rolling 30-days Average

Kushal_Chawda — Wed, 11 Oct 2017 13:32:52 GMT

see the attached

Re: Rolling 30-days Average

Anonymous — Wed, 11 Oct 2017 14:19:55 GMT

Thanks for your help Kushal. But I still didn't get the expected result. Is this possible only for a selected date?