Last 30 days rolling median

[baniabhc]

Hello everyone.

I'm new in qlik sense and need help.

I want to create a 30 last days rolling median in my table.

My table consists of FILENAME and FILESIZE and DELIVERYDATE.

Each FILENAME is delivered everyday. 

I have few FILENAMES and would like to count median of each FILENAME of last 30 days.

I mean:

ex. "A" file for 11.03.2024 has 1000mb, 

"A" file for 10.03.2024 has 790mb,

"B" file for 11.03.2024 has 200mb, 

"B" file for 10.03.2024 has 190mb,

My goal is to have 30 last days median displayed in each row next to the FILENAME, FILESIZE and DELIVERDATE

Thanks!

Regards

[LRuCelver]

Can you use this?

{<DELIVERYDATE = {">$(=Date(Max(DELIVERYDATE)-30))"}>} Median(FILESIZE)

You might have to add a date format string in the set analysis. 

[Chanty4u]

Try this

=RangeAvg(Above(TOTAL FILESIZE, 0, RowNo()), Above(TOTAL FILESIZE, 30, RowNo()))

[baniabhc]

I just copied it into my edit expression field and it says it has error. 

What do you mean by adding a date format string in the set analysis.

Regards 😄

[baniabhc]

It doesnt work. It shows everywhere that FILESIZE = 30D_MEDIAN

Median should be counted for each group of files. I mean median of A files for last 30 days, then median for B files for last 30 days.

Regards 😄

[LRuCelver]

If your date has a different format than the default, you have to format the expression with the same format:

{<DELIVERYDATE = {">$(=Date(Max(DELIVERYDATE)-30, 'DD.MM.YYYY'))"}>}

This formula will only show the Median of the last 30 days per file, not the rolling median that changes with the date.

You can easily calculate the rolling average for the last 30 days using this formula:

RangeAvg(Below(total FILESIZE, 0, 30))

But since there is no "RangeMedian" function, there is no easy way to calculate the rolling median.

I would do the calculation in the data load script instead.

[baniabhc]

Thank you very much for you help!

How would you calculate it in the data load script?

[LRuCelver]

Here is a small example:

//	Generating sample data
Data:
NoConcatenate Load
	Date(Today() + 1 - RecNo()) as DELIVERYDATE,
    RecNo() as FILESIZE
AutoGenerate 60;

Join Load * Inline [
FILENAME
A
B
];

//	Preparing intervals for IntervalMatch
Intervals:
NoConcatenate Load Distinct
	DELIVERYDATE as StartDate,
    Date(DELIVERYDATE + 29) as EndDate,	//	Every file contributes to the median for 29 days after it was delivered
    FILESIZE as FileSizes,
    FILENAME
Resident Data;

//	Joining interval start and end dates to Data table
Join(Data) IntervalMatch(DELIVERYDATE, FILENAME) Load
	StartDate,
    EndDate,
    FILENAME
Resident Intervals;

//	Using start and end dates to join the file sizes to the Data table
Join(Data) Load * Resident Intervals;

//	Calculating the median of the joined file sizes
Final:
NoConcatenate Load
	FILENAME,
	DELIVERYDATE,
    FILESIZE,
    Median(FileSizes) as ROLLINGMEDIANSIZE
Resident Data
Group By
	FILENAME,
	DELIVERYDATE,
    FILESIZE;

//	Dropping old tables
Drop Tables Data, Intervals;

[baniabhc]

Is this sollution possible when my data is loaded from database?

I cant LOAD * INLINE

Thanks!

Regards

[LRuCelver]

Where the data is coming from shouldn't matter.

But an inline load should work regardless