Show all possible dimension values in Pivot table

Coldie — Mon, 08 Jan 2024 19:06:33 GMT

Hello,

See load script below...
I'm trying to create a pivot table where all possible YearWeek values from the MasterCalendar table are shown.

However, the problem is that not all YearWeek contain Hours in the Data table. Thus the weeks where no hours have been logged are hidden.

I've tried to add "+ Sum({1} 0", i.e. Sum(Hours) + Sum({1}0), but for some YearWeeks there are no logged Hours at all so this doesn't help.

Maybe I need to create some type of bridge table but I don't know how in order to avoid incorrect Sum(Hours).

There are two additional requirements:
1. If the user selects an OrderID, the table should only show that OrderID while still displaying all YearWeek
2. Only if the user specifically applies a filter in the YearWeek dimension, the non-selected YearWeek should be hidden

The Date field uses YYYY-MM-DD as format.

MasterCalendar:
Load * Inline [
YearWeek,Date
2022-52, 2023-01-01
2023-01, 2023-01-02
2023-01, 2023-01-03
2023-01, 2023-01-04
2023-01, 2023-01-05
2023-01, 2023-01-06
2023-01, 2023-01-07
2023-01, 2023-01-08
2023-02, 2023-01-09
2023-02, 2023-01-10
2023-02, 2023-01-11
2023-02, 2023-01-12
2023-02, 2023-01-13
2023-02, 2023-01-14
2023-02, 2023-01-15
2023-03, 2023-01-16
2023-03, 2023-01-17
2023-03, 2023-01-18
2023-03, 2023-01-19
2023-03, 2023-01-20
2023-03, 2023-01-21
2023-03, 2023-01-22
2023-04, 2023-01-23
2023-04, 2023-01-24
2023-04, 2023-01-25
2023-04, 2023-01-26
2023-04, 2023-01-27
2023-04, 2023-01-28
2023-04, 2023-01-29
2023-05, 2023-01-30
2023-05, 2023-01-31
] (delimiter is ',');

Data:
Load * Inline [
OrderID, Date, Hours
12345, 2023-01-02, 3
12345, 2023-01-15, 2
23456, 2023-01-03, 4
23456, 2023-01-26, 2
] (delimiter is ',');

TIA

Re: Show all possible dimension values in Pivot table

pedrohenriqueperna — Fri, 12 Jan 2024 17:45:53 GMT

Hi,

You should have the option "include nule values" under "Add-ons" section of the table, otherwise if there's a complete null row it will not be shown (although it doesn't seem to be your case while looking at the table print).

Furthermore, try making an if condition like: If(IsNull(Sum(Hours)), 0, Sum(Hours))

This should replace every null value with a 0.

As for the requeriments, you could use the "show if" column based on the user selection, e.g,

GetSelectedCount(YearWeek) = 0 or GetSelectedCount(YearWeek) = 1 and GetFieldSelections(YearWeek) = YearWeek (something like this, not quite sure if it'd work using the YearWeek as a dimension column)

The same logic applies to the OrderID selection, although you'll probably have to consider both fields in the expression.

If you're able to provide a sample I could help you out more effectively

topic Re: Show all possible dimension values in Pivot table in App Development

Show all possible dimension values in Pivot table

Re: Show all possible dimension values in Pivot table