Pivot table, master calendar, missing dates

[PKPK90]

Hello, I am trying to create running calculation in pivot table (PSI), but for null values it is not showing data. I have master calendar created it is connected to data table, but still it is not showing 'null' values for missing data rows and PSI calculation is not available, it is running but only for columns with values available. 

PSI:

Sum(
Aggr(
RangeSum(
Above(
Alt(Sum(SUPPLYQTY),0) - Alt(Sum(DEMANDQTY),0)*-1,
0,
RowNo()
)
),
ITEMID,
YearWeek
))

DataLoadEditor:

PSI_V1:

SELECT RQ.*, 

INV.PRIMARYVENDORID,

INV.BOMUNITID,

INV.NAMEALIAS,

INV.PRODGROUPID,

INV.ITEMBUYERGROUPID,

IBV.DESCRIPTION,

IBV.NAME,

IDM.INVENTLOCATIONID,

IDM.INVENTSITEID,

IDM.wMSLocationId,

//INV.KCS_PRIMARYVENDNAME,

//INV.KCS_STRATEGICBUYERGROUPID,

    CASE RefType

        WHEN 0 THEN 'None'

        WHEN 1 THEN 'InventOnHand'

      ....

ORDER BY RQ.DATAAREAID, RQ.ITEMID, MRPDate_V1, MRPSort;

PSI:

LOAD *,

     Date(Floor(

         IF(REFTYPE = 1 OR REFTYPE = 14, 

            Date(Today()), 

            REQDATE)) // to samo co MRPDate_V1 w SQL

     ) AS MRPDate

     RESIDENT PSI_V1;

DROP TABLE PSI_V1;

TAG FIELD RQ.REQDATE WITH '$date';

TAG FIELD RQ.REQDATEDVOGIR WITH '$date';

.....

MinMaxDates:

LOAD 

    Min(REQDATE) AS MinDateRaw,

    Max(REQDATE) AS MaxDate

RESIDENT PSI

WHERE REQDATE > MakeDate(1900,1,1);  // Ignorowanie daty 1900-01-01

LET vMinDateRaw = Peek('MinDateRaw', 0, 'MinMaxDates');

LET vMaxDate = Date(Peek('MaxDate', 0, 'MinMaxDates'));

// Jeśli minimalna data jest NULL (brak rzeczywistych dat), ustaw domyślną wartość

LET vMinDate = IF(Len('$(vMinDateRaw)') > 0, Date('$(vMinDateRaw)'), NULL);

LET vMinDateNum = Num(Date#('$(vMinDate)', 'MM/DD/YYYY')); 

LET vMaxDateNum = Num(Date#('$(vMaxDate)', 'MM/DD/YYYY'));

MasterCalendar:

LOAD

    Date($(vMinDateNum) + IterNo() - 1) AS MRPDate,

    Week(Date($(vMinDateNum) + IterNo() - 1)) AS WeekNum,

    WeekYear(Date($(vMinDateNum) + IterNo() - 1)) AS WeekYear,

    Month(Date($(vMinDateNum) + IterNo() - 1)) AS MonthName,

    Month(Date($(vMinDateNum) + IterNo() - 1)) AS MonthNum,

    Year(Date($(vMinDateNum) + IterNo() - 1)) AS Year,

    Year(Date($(vMinDateNum) + IterNo() - 1)) * 100 + Week(Date($(vMinDateNum) + IterNo() - 1)) AS YearWeek,

    QuarterName(Date($(vMinDateNum) + IterNo() - 1)) AS Quarter,

    Date(MonthStart(Date($(vMinDateNum) + IterNo() - 1))) AS MonthStart,

    Date(WeekStart(Date($(vMinDateNum) + IterNo() - 1))) AS WeekStart,

    Day(Date($(vMinDateNum) + IterNo() - 1)) AS Day,

    WeekDay(Date($(vMinDateNum) + IterNo() - 1)) AS WeekDayName,

    If(Match(WeekDay(Date($(vMinDateNum) + IterNo() - 1)), 'Sat', 'Sun'), 1, 0) AS IsWeekend

AUTOGENERATE 1

WHILE $(vMinDateNum) + IterNo() - 1 <= $(vMaxDateNum);

DROP TABLE MinMaxDates;

TAG FIELD MRPDate WITH '$date';

TRACE vMinDate: $(vMinDate);

TRACE MaxDate: $(vMaxDate);

[Or]

You seem to be using a field from the main table (ITEMID) in your AGGR(), so this behavior is as expected. You'd need to change that if you want to show cases where that field is missing.

[PKPK90]

I would like to show 'all' weeks from Master Calendar table and run PSI calculation for all weeks (even with null values). So make Master Calendar to filter PSI table, not reverse. Is that possible?

[marcus_sommer]

It's not possible - at least not in a sensible way. The tables are associated and therefore the missing key-values respectively the NULL's will impact each other. In some scenarios you may get the NULL's with alt() or range-functions and/or {1} set analysis and/or calculated dimensions which enforce the entire dimension-table within the object. But the needed efforts and the complexity could become quite high and will also cause various side-effects in regard to the performance and the usability.

Or in other words you will always need a dimension-value to show a calculation-result against it - and if the measures come from the facts all wanted dimension-values must be within the facts. To have them in the dimensions is not sufficient.

The most practically workaround is usually to populate the missing data.

Beside this the mentioned NULL's are neither a technically nor a logically error which must mandatory be resolved - else the NULL (gaps in the charts and grey field-values in the selection-objects) itself is a valuable information.