Can I show different types of values depending on the Month?

[Frank]

I’d like to see sales for the closed months and the forecast for the remainder of the months, giving me an Outlook for the year.

This can be accomplished in Stratum using a Calculated measure that looks at the month name and determines if it is a past month, the current month or a future month.

Step one is to have a hidden measure that captures the Month name – this is used in the logic for the Outlook calculation we will build. In the example I’m using Year Based Month Based, you;ll need to use a Based time dimension for this technique I’m describing. The following will get you the month name:

________________________________________________________________________________
//Only show if there is data in the Baseline
Iif([Measures].[Data1 (Sales)]<>0 OR [Measures].[Data3 (Fcst)]<>0,
[Time].[Year Based Months Based].CurrentMember.name
,null)

The first line, starting with // is a comment
The Iif is just a check to prevent this from being done if there is no data- you could leave this out becasue this measure will be hidden – but I typically put them check in there so if I need to debug things I can show measure while making changes, then hide it again once I’m done.
The [Time]…. line is the actual retrieval of the dimension member name – the month names are the Based names, like “2 Months Ago”, “3 Months Ago”, “Current Month”, “Last Month” etc. This is handy so we can check for past, current or future months by looking at these.

If you are using std. Stratum calendars you should be able to lift this and use it verbatim.

________________________________________________________________________________

Once we have the month name now we can use it in our Outlook calculated measure to decide if we will show Sales (for the past months) or the forecast for the current and future months.

That calculation looks like this:

________________________________________________________________________________

//Only preform this if there is data, this prevents the
//Calc from happening on null data combos

Iif([Measures].[Data1 (Sales)] <> 0 OR [Measures].[Data3 (Fcst)] <> 0,

//Check the month name to decide if we want to display
//Actual Sales or the forecast

Iif( [Time].[Year Based Months Based].CurrentMember.name = “Current Month” OR [Time].[Year Based Months Based].CurrentMember.name = “Next Month” OR (INSTR(1, [Time].[Year Based Months Based].CurrentMember.name, “Out” )<> 0),[Measures].[Data9 (Outlook – Fcst or Open)],

//If it’s not current or a future month show sales
[Measures].[Data8 (Sales + Open Orders)]),

//Show null if the there is no data for the sales or forecast
null)

________________________________________________________________________________

Same comment as earlier on the lines with // and the first Iif statement.

The next Iif statement checks for 3 things – if it is the Current Month, Next Month or any month with the word Out in the name – as in 2 Months Out, 3 Months out etc. If any one of these is true we will display the forecast which is in the measure Data9. In my example Data9 also has some built in logic to evaluate which is greater – the forecast or the current open orders – if anyone is interested I can provide that logic in another post.

The else (after the “,”) will cause the actual Sales for the past months to display – using Data8.
________________________________________________________________________________

You can use this technique for many things – in this case I looked for current and future months, but it could easily be changed to look at prior months.

You could also use this technique to look for certain products,customers or other dimensions – there was a post on this technique here

Enjoy! I’ll post a screen shot of the result shortly.

[Frank]

Here is a screen shot of the a view using the techniques described in the last post – the highlited line is the Outlook created using the calcs described.

• This reply was modified 2 years, 11 months ago by Frank.
• This reply was modified 2 years, 11 months ago by Frank.

[Author]

Posts