Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!