Create a target column alongside the time columns derived from time dimension

Questions & Answers
1

Hi everyone,

I am trying to create a report that has time dimension in the columns. I created it using the ‘Pages’ feature of the Time dimension using one week as the reference. This way I have a breakdown of the measures in rows by day and a total in the week (first) column.
What I need to do is add two additional columns:

  • one containing weekly targets for each measure in each row
  • one calculating the variance between this target and the actual total in the week column
    Any ideas on how to do this would be much appreciated.
    I am attaching an image with the report so far…

image
image.png1920×1080 243 KB

2

I would suggest usinf Time dimension calculated members to create columns for Planned and Actual vs Planned . You will lose the option use Time on Pages, though. I would suggest using some calculated members to get to a required week to columns dynamically.

You can use This week , Previous Week , or any other calculated members on week day level there instead of current pages selection.
Here is an example for This week that will give you days of the current week:

Aggregate( ChildrenSet([Time.Weekly].[Week].CurrentDateMember) )

Create similar for Planned and Planned vs Actual , however, use a bit different formula:

Aggregate( [Time.Weekly].[Week].CurrentDateMember )

Please use correct Time weekly hierarchy for calculated members otherwise this calculated member will throw you an error.

Then you should update each Measure you are using on Rows . Please add different scenarios for Planned, Actual, and Planned vs Actual. Here is an example calculation, where I have a different case for Planned and “regular”:

CASE WHEN 
[Time].CurrentHierarchyMember.Name = "Planned" 
THEN 
10 -- target 
WHEN
[Time].CurrentHierarchyMember.Name = "Planned vs Actual" 
THEN
10-[Measures].[Issues created] 
ELSE 
-- regular scenario
[Measures].[Issues created] 
END

The formula above uses names Planned, Planned vs Actual of Time dimension calculated members.

Here is an example report using calculated Time members Planned and Actual:

report%20example-2
report example-2.png2078×474 53.3 KB

Daina / support@eazybi.com

3

Hi Daina,

Thank you very much for your answer.

If I make this per week, can I have the customer pick the week. Something like what the ‘Pages’ function offers. What if I put this report into a dashboard, can the week be passed as parameter to the report from the dashboard?

How about if we make the report to contain one day only. Can the day be passed as a parameter directly or through a dashboard?

I am saying this because having the used to select a certain interval (or week), or a certain day in eazyBI is not that easy?

Thank you,
Radu