Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am using a fact table where my performance is stated in €. In this fact table there is a key to Calendar, and the Key is made with autonumber(Date), so it is on the most possible granularity.
Unfortunately I don't know how to add the Target data, which I have on weekly basis to this scheme, due to the Key which is on daily basis and not on weekly!!!
I am sure this is issue which can be solved easily for experts.
Can anybody give me a tip please?
Kind Regards,
Chris
See this blog post: Fact Table with Mixed Granularity
In a simple modification of your data model you could do something like this:
That way the associative model helps you get hold of the weekly target however you select in your application. But you have to make the expressions accordingly so you don't sum up the weekly target for each day of the week of course.
First of all I hope you have weekyear or yearweek. if so create a date Field of this, the lowest granularity here will be the date when the week start. When you have the weekstart Field, concatenate Target data With Fact table With this key so it will be connected with Your calendar.
In presentation view, GUI, dont present day in a listbox, week should be the last Level.
weekstart(WeekName(MakeWeekDate(Left('201801',4), Right('201801',2)))) as Datekeytocalendar replace the date here With Your yearweek Field. Also wrap autonumber around to get same format as in Fact.
you can test this in a textbox before you script it in.