Author Topic: Actual vs. Plan vs. Prior Year  (Read 178 times)

Offline Stephaneky

  • Full Member
  • ***
  • Join Date: Jan 2018
  • Posts: 18
  • Forum Citizenship: +0/-0
Actual vs. Plan vs. Prior Year
« on: 27 Jul 2018 11:33:16 am »
In 10.2.2 report studio I have a combination chart that displays Actual Sales against Plan but also includes Prior Year sales (see attachment).


Data comes from a virtual cube and both actual and plan respond appropriately showing values MTD for the last month displayed. For the prior year data, I use the following expression but I am unable to show pro-rated month values (i.e. the expression below returns value for the complete time period)


aggregate(tuple([Actual Face Amt],parallelPeriod([Sales].[Time].[Time_H].[Year],1,currentMember([Sales].[Time].[Time_H]))) within set [CBU/Product Type Series])

To clarify the issue, the stacked bars represent sales by month, the line represents plan for the same period and the dot represents prior year sales for the same period. Both Bar and Line display pro-rated values (i.e. on the 5th of July, sales & plan are pro-rated to 5 days, but the prior year value is displaying prior year sales for the entire month of July in the prior year).

I need to modify the expression to pro-rate the prior year data to do the same (i.e. only show sales up to the 5th of July of the prior year).

Any help would be greatly appreciated.

Offline sdf

  • Statesman
  • ******
  • Join Date: Feb 2014
  • Posts: 396
  • Forum Citizenship: +3/-0
Re: Actual vs. Plan vs. Prior Year
« Reply #1 on: 27 Jul 2018 12:31:11 pm »
first idea is to use add-years([Sales].[Time].[Time_H],-1)

have not tried but this comes first to my mind.

Offline Stephaneky

  • Full Member
  • ***
  • Join Date: Jan 2018
  • Posts: 18
  • Forum Citizenship: +0/-0
Re: Actual vs. Plan vs. Prior Year
« Reply #2 on: 27 Jul 2018 01:15:58 pm »
Thank you sdf,

Isn't the yearly offset taken care of by this part of the expression?

parallelPeriod([Sales].[Time].[Time_H].[Year],1

Are you suggesting I incorporate your addition to the second argument?  currentMember([Sales].[Time].[Time_H])))

Offline sdf

  • Statesman
  • ******
  • Join Date: Feb 2014
  • Posts: 396
  • Forum Citizenship: +3/-0
Re: Actual vs. Plan vs. Prior Year
« Reply #3 on: 27 Jul 2018 01:37:28 pm »
first of,

what's this item "parallelPeriod([Sales].[Time].[Time_H].[Year],1,currentMember([Sales].[Time].[Time_H])))" output?
I am guessing it only results to year and month. Thus why you are getting the total of that month instead of the pro rated day.

this is what I have in mind, if [Sales].[Time].[Time_H] returns full YYYY-MM-DD.

aggregate(tuple([Actual Face Amt],add-years([Sales].[Time].[Time_H],-1)) within set [CBU/Product Type Series])

you can try this.

Offline Stephaneky

  • Full Member
  • ***
  • Join Date: Jan 2018
  • Posts: 18
  • Forum Citizenship: +0/-0
Re: Actual vs. Plan vs. Prior Year
« Reply #4 on: 27 Jul 2018 02:35:45 pm »
I appreciate the input sdf...

I am getting this error when modifying the expression

The argument at position 2 of 'tuple' from the data item 'Prior Year Actuals for Months' is a 'value' expression, which cannot be coerced into a 'member' expression

Offline sdf

  • Statesman
  • ******
  • Join Date: Feb 2014
  • Posts: 396
  • Forum Citizenship: +3/-0
Re: Actual vs. Plan vs. Prior Year
« Reply #5 on: 27 Jul 2018 02:58:43 pm »
are you on DQM?
« Last Edit: 27 Jul 2018 03:08:59 pm by sdf »

Offline Stephaneky

  • Full Member
  • ***
  • Join Date: Jan 2018
  • Posts: 18
  • Forum Citizenship: +0/-0
Re: Actual vs. Plan vs. Prior Year
« Reply #6 on: 27 Jul 2018 03:46:24 pm »
yes we are on DQM.

 


       
Twittear