topic Compare current & previous periods with column dims in App Development

Compare current & previous periods with column dims

dana — Thu, 12 Jan 2023 16:47:27 GMT

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!

Re: Compare current & previous periods with column dims

vinieme12 — Fri, 13 Jan 2023 08:23:31 GMT

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)

Re: Compare current & previous periods with column dims

dana — Fri, 13 Jan 2023 12:40:40 GMT

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!

Re: Compare current & previous periods with column dims

dana — Fri, 13 Jan 2023 15:39:13 GMT

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!