Latest 4 week rolling value

[rworthmann681]

Hi

I have a expression that calculates the latest sales value by customer and brand in a particular period. What I need now is to use this expression, and calculate all the latest values by customer and brand within a 4 week rolling period.

Below is the result I need

I have tried various options, but it always only accumulates.  This would be ideal, just if there is another option than RangeSum

Count({1<[Week Ending]={">=$(=Min([Week Ending]))<=$(=Max([Week Ending]))"}>} DISTINCT 1) *
RangeSum(Above(Sum({1}0) + LatestAvailNP, 0, 4)).

This is my initial expression that calculates the LatestAvailNP measure:

// Define a variable to detect which time field is present
If(
WildMatch(GetObjectField(0), 'Date')
or WildMatch(GetObjectField(1), 'Date')
or WildMatch(GetObjectField(2), 'Date'),

// Date is in use
If(
Dimensionality() = 0,
// Total row
Sum(
Aggr(
If(
NumDate = Max(TOTAL <CUST_CD, [Brand Name]> NumDate),
Sum(AVAIL)
),
CUST_CD, [Brand Name], NumDate
)
),
// Row level
Sum(
Aggr(
If(
NumDate = Max(TOTAL <CUST_CD, [Brand Name], Date> NumDate),
Sum(AVAIL)
),
CUST_CD, [Brand Name], Date, NumDate
)
)
),

// Week Ending in use
If(
WildMatch(GetObjectField(0), 'Week Ending')
or WildMatch(GetObjectField(1), 'Week Ending')
or WildMatch(GetObjectField(2), 'Week Ending'),

If(
Dimensionality() = 0,
Sum(
Aggr(
If(
NumDate = Max(TOTAL <CUST_CD, [Brand Name]> NumDate),
Sum(AVAIL)
),
CUST_CD, [Brand Name], NumDate
)
),
Sum(
Aggr(
If(
NumDate = Max(TOTAL <CUST_CD, [Brand Name], [Week Ending]> NumDate),
Sum(AVAIL)
),
CUST_CD, [Brand Name], [Week Ending], NumDate
)
)
),

// Period in use
If(
WildMatch(GetObjectField(0), 'Period')
or WildMatch(GetObjectField(1), 'Period')
or WildMatch(GetObjectField(2), 'Period'),

If(
Dimensionality() = 0,
Sum(
Aggr(
If(
NumDate = Max(TOTAL <CUST_CD, [Brand Name]> NumDate),
Sum(AVAIL)
),
CUST_CD, [Brand Name], NumDate
)
),
Sum(
Aggr(
If(
NumDate = Max(TOTAL <CUST_CD, [Brand Name], Period> NumDate),
Sum(AVAIL)
),
CUST_CD, [Brand Name], Period, NumDate
)
)
),

// Default: no time field detected
Sum(
Aggr(
If(
NumDate = Max(TOTAL <CUST_CD, [Brand Name]> NumDate),
Sum(AVAIL)
),
CUST_CD, [Brand Name], NumDate
)
)
)
)
)

Please help!

[Kaushik2020 · Accepted solution]

you can try to handle this through the load editor. Below is a sample code. here if the sales is 0, then we will take the last sales amount and show in Qlik Sense. 

Hierarchy:
Load * Inline [
WeekEnding, LatestSales
02-12-2024, 15
09-12-2024, 0
16-12-2024, 7
23-12-2024, 0
];

FinalLoad:
Load
WeekEnding,
LatestSales,
If(LatestSales = 0, Peek(MeasureOutput), LatestSales) as MeasureOutput

Resident Hierarchy;
Drop table Hierarchy;