Hi Gurus,
I have a simple D-cube for Sales.
Sales versions, Time, Flag for Actual /Forecast.
With every completed month, I want to update the cube intersection input value from 'Forecast' to 'Actuals'.
The intersection is based on Month and version. The cube already has the input as 'Forecast' till the year end. How do I automate the change from 'Forecast' to 'Actuals' as each month closes?
Did thought about D-Links and Allocation Tables but I am not sure how to achive this at the cube intersection..
Apprecaite your response.
Thanks.
Hi 'World,
Based on my understanding of your requirement, I just have a question: Your Flag for Actual / Forecast is what? Just the text Actual for Actual and Forecast for Forecast? If the requirement is to just set the new flag once the month closes automatically based on the date you'll likely want to build a macro, or possibly 12 macros, that will automatically run against a windows scheduler. This is because, to the best of my knowledge, Analyst is not 'date aware'..
If someone knows of a better or more elegant way to accomplish this, then I imagine this is the right place to ask. ;)
Eric
Yes, the Flag value is text value "Forecast/Actuals". I am still not able to understand, how a macro will take care of updating this. The value is being changed at the intersection, so not sure how a macro is going to do that..
And the Flag D-List is a formatted D-List. so that the user don't have to type in the entire word "Actuals" or "Forecast", with A+Enter populates "Actuals" and F=Enter populates Forecast. If this adds anything to the point in question..
Hey 'World,
Sorry I should have elaborated further. You would use the @DCubeInput macro command. It would look something like this:
@DCubeInput("Actual";["Sales Versions","Time","Flag Actual/Forecast"])
Where Sales Versions, Time, and Flag Actual/Forecast are your dimension intersection. As with your other thread regarding the locks, protects, etc.. you might be able to code in a variable to have it prompt you for the selection to apply the flag update to.
Thanks Eric.
I tried to edit the list and provided the D-List names in the list editor. That approach didn't worked. Also, tried to give the actual intersection values in the same order as it shows in the objects used. This one also failed due to the same error.
And even if one of the above approach worked, the main question is how to make Analyst know (as you said "aware") to test it like an if condition.
Essentially, I want to tell Analyst.:
IF Month <= most recent completed month, then update the intersection of September and Forecast as "Actuals" else, let it remain as "Forecast"
Thanks.
Hey 'World,
Unfortunately I'm not aware of any method you can use to setup logic to automatically switch over the Forecast to the Actual flag. As I mentioned, as far as I know, Analyst has no way of telling what the date is.
If this isn't a possibility out of the box, maybe someone else on here has a custom method to work around this.
Got this resolved.
Created a table in the database which evaluates the condition
IF Month <= most recent completed month, then update the intersection of September and Forecast as "Actuals" else, let it remain as "Forecast",and used this table as a data source in the D-Link and the target as the cube in question.
The data source table has a column for Forecast/Actuals which has Forecast and Actual values, and using the Mark Data the values collapse to DATA, then used the allocation with the cube Flag. Ran the D-Link. Works as expected. So whenever the process is run, and if the Month is complete, the table will update the "intersection" value with "Actuals" text and remaining months stay as "Forecast" text.
Thanks Eric for all your responses!!
You're welcome!
Not only did you come up with a unique way to address this requirement, but you may have helped someone else as well. You deserve some kudos too. ;)
Hello World,
Sorry to be late to the party, but have a look at switchover dates and the @Mix BiF... this function takes 2 inputs (actual and forecast) and is actual until the switchover date set on your Timescale.
Analyst is time-aware on dimensions with Timescale information set up, but using it can get a bit arcane and inevitably involves busting out the BiFs. The 300-page chapter on 'em in the Analyst Modelling guide is well worth printing and keeping as desk reference, there's no way to learn the things except for using them.
You might also want to have a look at @Outlook, @Drive, @Drive1, @Drive2, @DCF, @ICF, @Grow and @Stockflow, a quick flick through says they all use the Switchover date too but then do more.
Thanks Eric. Thanks Rutulian for the BiF solution. I haven't tried it, but overall I think that is also a workable idea.
Thanks for sharing.