topic Re: Pivot table, master calendar, missing dates in App Development

Pivot table, master calendar, missing dates

PKPK90 — Tue, 27 May 2025 13:02:26 GMT

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);

Re: Pivot table, master calendar, missing dates

Or — Tue, 27 May 2025 13:26:53 GMT

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.

Re: Pivot table, master calendar, missing dates

PKPK90 — Tue, 27 May 2025 14:19:05 GMT

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?

Re: Pivot table, master calendar, missing dates

marcus_sommer — Tue, 27 May 2025 15:20:09 GMT

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.