Compare current & previous periods with column dims

[dana]

Hi People,

I need to design a pivot which will enable comparing 2 periods, with a dynamic selection of periods.

Current period can be from date - to date, based on the user selection in the calendar.

Previous period - is calculated for the previous year.

So the same expressions are displayed for both periods.

I would appreciate some tips for achieving it.

Thanks!

[vinieme12]

Use a Calculated Dimension in this case

Row Dimension

=Supplier

Column Dimension

Period (level1)

=  Aggr( only({<Datefield = {">=$(=YearStart(Max(Date)))<=$(=Max(Date))"}>+<Datefield = {">=$(=YearStart(Max(Date),-1))<=$(=AddYears(Max(Date),-1))"}> }Datefield )   ,Datefield)

[dana]

Hi Vineeth,

After applying the expression, looks like the ranges are calculated properly:

  The issues are:

Current period shows only the month selected, and not the range.

The previous periods are calculated properly, but not displayed at all in the pivot.

Please see the filter object

I can't figure out the reason for that..

Edit: 

In the pivot table, I don't want to show the months, but the relevant range only...

Is it feasible? 

How would the table expression sync with it?

Will appreciate your help.

Thanks a lot!

[dana · Accepted solution]

Hi,

I defined two variables for the current and previous periods:

vCurrentPeriod: 

=Dual(Date(YearStart(Min(MonthDate))) & '-' & Date(Max(MonthDate)),2)

vPreviousPeriod:

= Dual(Date(YearStart(Min(MonthDate),-1)) & '-' & Date(AddYears(Max(MonthDate),-1)),1)

I used ValueList to create my periods dimension in the pivot table:

=ValueList(vCurrentPeriod,vPreviousPeriod)

I added to the expressions an If:

=If(ValueList(vCurrentPeriod,vPreviousPeriod) =vCurrentPeriod,
Sum({<Year = {'$(vCurrentYear)'},Date={"<=$(vMaxDate)"}>}CountEntries_Monthly),
Sum({<Year = {'$(vPreviousYear)'},Date={"<=$(vMaxDate_PrevYear)"}>}CountEntries_Monthly))

And it does what I want!