I have sales "detail" tables that provides information down to the stock code level by day, but I have a budget file that is only at the Product Class level for each month. Each stock code has a Product Class. I need to see detail information and also summary information compared to budget. My linking current creates a one to many problem. How do I do this?
I would concatenate everything on a single Fact Table. I woudl associate the budget with the first date of the month (could be anydate, actually).
The budget will have null values for the stock code dimension.
I would solve the rest on the interface.
I only allow the user to compare sales and budget at the Product Class and Month level.
If the user selects a stock code, the budget will be null, which is ok because there is no budget at this level.
If the user selects a day, he will not get a budget either, because there is no daily budget.
I included an application to show this in action.
I used different fields to include the sales result (VALUE) and BUDGET. You could use a single field to put the number and an additional field with a flag.
I describe them, but also included on the example file:
Budget1:
sum( {<SKU= >}BUDGET) - ignores the selecion on SKU. This may generate results for others lines
Budget2:
= IF( SUM(VALUE)>0, sum( {<SKU= >}BUDGET)) - this will show the buget of the category for the product associated with the SKU (check an error at the total line)
As an extension to the answer, if SKU 1 is selected again, Budget3 returns 100 for the January Period.
Would it be possible to amend Budget 3 such that it returned the value for Feb as well? basically returning the Budget value for Product Class A for all months based on the SKU selection?
I have checked your most recent suggestion and the code works.
I have one further complication, there's an additional field attached to the Budget table called "Country". Using SKU 1 as the filter, your formula returns the correct result as follows:
Period Value Budget
Jan 50 100
Feb - 110
I am trying to get the following result, without much success with my limited knowledge of set analysis, and have tried to modify your suggestion but afraid I am probably doing more harm than good.
Where a user makes a selection on Country, I need the Value to stay static at 50, but the Budget to reflect the selection on Country, as follows:
Period Value Budget Country
Jan 50 100 =X
Feb - 110
Period Value Budget Country
Feb 50 110 =Y
Ultimately, the Value will be divided by the Budget to get a ratio in a graph format.
I think the actuals should have the same dimension. Then it would make more sense to compare it with the budget.
If you need to keep it like this, I'm afraid my solution will not be so general as the others.
I created a second country field (refCountry) which is a data island (disconnected from the model). This way you can make selections in sku and still have something to select on country (or refCountry).
I reconnect this field only on the expression level using p().
I say that for the budget, I must not consider sku (because this level of detail does not exist) but I say the country must obey the selection in refCountry.
Maybe it's better to understand it at the application.
Thanks for the quick response Erich. Your solution definitely works based on the sample qvw.
My application is more complicated that the example with many more "Country" type fields, therefore may not be suitable.
I may have to retest the calendar island solution with the two fact tables joined by the ProdClass using SetAnalysis, which I hope would perform better than the IF statement.
Let you know how I go. Thanks for all your generous help.
