Sum of Previous Values in Pivot Table (missing records for 0 values)

[BartVA]

For a table like below, how would you make a (master) measure that shows Sum(Value) for the previous week?

Is it even possible to do so without filling out the facts table first, so that it has for each date an entry for each SKU and each StoreID? Note that SKU's can appear and disappear during certain weeks, since there is no entry when Value is 0. For instance SKU "B" has no entry for StoreID 2 on Date 10, "C" only appears on Date 24 in one StoreID etc.

tFacts:
Load * Inline [
StoreDate, SKU, StoreID, Value
10, A, 1, 100
10, A, 2, 110
10, B, 1, 300
17, A, 1, 110
17, B, 3, 200
24, A, 1, 110
24, C, 1, 1000
];

[Or]

While it is possible to do this without creating the values in script, the recommended approach is to create values in script. If you can't do that for some reason, you can achieve this using ValueLoop() or Chart-level scripting, I believe.

[BartVA]

To partially answer my own question, I found in this older forum answer by a Qlik employee  that if you use Date as Columns, the measure

Before(Sum(Value))

works fine, without filling out the missing records (zero values) in the table. I tested it and it does.

However, I haven't found a solution if you put Date as Rows for instance.

[Or · Accepted solution]

This is thoroughly nasty and could probably be cleaned up some, but again, the recommended approach here is to run this in script, not on the front end... Do note that you may need to adjust the code for multiple dimensions, this example is for using just the date dimension.

Dimension:

ValueLoop(Min(Total StoreDate),Max(total StoreDate),1)

Measure:

If(
Count(If(StoreDate = ValueLoop(Min(Total StoreDate),Max(total StoreDate),1),Value))>0,
Sum(If(StoreDate = ValueLoop(Min(Total StoreDate),Max(total StoreDate),1),Value)),
Above(Column(1)))

Note that the self-reference using Column(1) is to get the previous value.

Option two, using chart level scripting, uses the same dimension and a measure (named Value) of:

Sum(If(StoreDate = ValueLoop(Min(Total StoreDate),Max(total StoreDate),1),Value))

Script:

Let P = HCNoRows();
For J = 1 to P
Let Val = HCValue(Value,J);
if Val = 0 then
Put Value(J) = HCValue(Value,J-1);
end if
Next;

// Note: This only works 0 and null are the same. If there are cases where there's really 0 and you want to see 0 as the value, you'll need to nullify the measure when it's missing similar to what I did in the first version

// [Edit] Second note: You can't use Chart Level Scripting in a pivot table, so this would be if you wanted to use a Table or some other object.