Using ValueList in Pivot for current and previous periods not working as expected

dana — Fri, 20 Jan 2023 10:44:34 GMT

Hi People,

I use Valuelist to add a period column to a pivot table: P1 and P2.

P1 for current period, P2 for previous period - calculated based on the user selection.

The pivot shows # of entries by category code, for each supplier.

When no year is selected - the pivot works fine.

It shows a supplier previous period data even if he doesn't have data in the current period.

for example, supplier no. 1:

But if a year is selected, this supplier is not displayed any more in the pivot.

But he is displayed in a table below the pivot, where no period column is used:

For each supplier, I added the missing periods with 0 values for entries, and it resolved the issue.

But I don't understand why.

My questions are:

Why this works and Is there a better solution?

My expressions format is:

//Current Period

Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(YearStart(Max(DateKey)))) <=$(=Max(DateKey))"}>}Entries)

//Previous Period

Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(YearStart(AddYears(Max(DateKey),-1)))) <=$(=Num(AddYears(Max(DateKey),-1)))"}>}Entries)

Thanks!

topic Using ValueList in Pivot for current and previous periods not working as expected in App Development

Using ValueList in Pivot for current and previous periods not working as expected