Multiple Budgets per Product

[avastani]

Hi,

I have a situation where Sales data is at a ProductDetail level while the Budget is at ProductGroup Level. Also, my Budget data as different budgets per ProductGroup i.e. Budget1, Budget2, Budget3.

The user would like to be able to pick a Budget Type and view sales performance in relation to BudgetType selected. The ProductGroup sits in the Product table as a field.

How would I handle this? Thanks for your replies

[#2]

Hi

There are so many different ways to do this that it is difficult to start listing them all, what I've done is to put together a quick example of one very simple way to achieve this. In the attached example, just change the selection of Budget 1,2,3 to see the different results.

Primarily what you will be looking for is a method that fits well with the look & feel that you want to achieve, so a lot depends on that.

Take a look at the load script, then at the objects and let me know if this fits your requirements.

Regards,

[avastani]

Hi Nigel, That's excellent. thanks bud!! Now the real problem is, that I have Budget on a MonthYear level while I have Sales on a daily level. So in essence I have to build a link table where the link table is really my calendar table plus Keys from the Budget. But what will that key be? That's where I fall apart.

[#4]

Hi Amir,

My general rule on linkiing up budgets and actual is to assign the budget a date equal to the first day of the month period and eliminate the need for linking. Especially when budget other dimensions with actual besides time... like customer or product.

Regards,

[#5]

Hi Amir

Please see attached example, this uses a two step process in the load script to get the derived columns required for keys, the problem I have here is that the method to use in the load script really does depend a lot on what/where your source data is, if it is in SQL somewhere then you shouldn't need the two step process I have here.

Link tables should not be required in this instance, they should be used only when complex linking need to take place, and this doesn't count as complex.

Take a look at the attached, if you need more information it is probably best to give me a bit more to work with, i.e. your current load script, sample QVW or just some more info on what you're script is currently doing.

Cheers,

[Anonymous]

Hi Nigel,

I too have this situation with an actuals table on day level and budget table on month level. Both fact tables share a couple more dimensions. My calendar table is on day level. (all in sql server)

Could you please put the script in a word document or explain the principles you used? I have qv personal edition and thus cannot open your qvw file.

Thanks!

[#7]

Hello Carlos

Here is the script I used in the above example:

ProductDetail:
LOAD * INLINE
[Product,ProdGroup,Sales,SalesDate,Price,Quantity,YTD
a,g1,28374,2009-01-03,1.25,1653,18954
a,g1,26635,2009-03-05,2.65,1088,21569
b,g1,2443,2009-04-07,3.65,995,13654
c,g2,7746,2009-02-21,4.22,1487,22635
d,g2,77465,2009-03-14,6.55,1320,41522
d,g2,8271,2009-06-13,7.01,1256,84551
e,g3,99283,2009-07-11,99.1,125,13326];
ProductDetailDerived:
LOAD Month(SalesDate)
 & ';'
 & Year(SalesDate)
 & ';'
 & ProdGroup AS KEY_BudgetDate
, Product
, ProdGroup
, Sales
, SalesDate
, Price
, Quantity
, YTD
RESIDENT ProductDetail;
DROP TABLE ProductDetail;
ProductGroups:
LOAD * INLINE
[ProdGroup, GroupDescription
g1,Product Group 1
g2,Product Group 2
g3,Product Group 3];
Budgets:
LOAD * INLINE
[ProdGroup, Budget1, Budget2, Budget3, BudgetDate
g1,5000,3000,4000,2009-01-01
g1,5000,3000,4000,2009-02-01
g1,5000,3000,4000,2009-03-01
g1,5000,3000,4000,2009-04-01
g1,5000,3000,4000,2009-05-01
g1,5000,3000,4000,2009-06-01
g1,5000,3000,4000,2009-07-01
g2,3000,3500,5000,2009-01-01
g2,3000,3500,5000,2009-02-01
g2,3000,3500,5000,2009-03-01
g2,3000,3500,5000,2009-04-01
g2,3000,3500,5000,2009-05-01
g2,3000,3500,5000,2009-06-01
g2,3000,3500,5000,2009-07-01
g3,1000,1500,2000,2009-01-01
g3,1000,1500,2000,2009-02-01
g3,1000,1500,2000,2009-03-01
g3,1000,1500,2000,2009-04-01
g3,1000,1500,2000,2009-05-01
g3,1000,1500,2000,2009-06-01
g3,1000,1500,2000,2009-07-01];
BudgetsDetail:
LOAD Month(BudgetDate)
 & ';'
 & Year(BudgetDate)
 & ';'
 & ProdGroup AS KEY_BudgetDate
, Budget1
, Budget2
, Budget3
RESIDENT Budgets;
DROP TABLE Budgets;
BudgetCodes:
LOAD * INLINE
[BudgetCode
Budget 1
Budget 2
Budget 3];
ValueBreakDown:
LOAD * INLINE
[ValueType
Sales
Price
Quantity
YTD];

This might/might not be enough for you to get going with, if you need a more focused answer relating to your own situation then you need to give me some more info to work with. It would be good to get an idea of your current tables, so if you create some LOAD * INLINE statements to replicate your layout then I might be able to help more specifically.

The key, always, in QlikView is to design your start schema with only single field links between appropriate tables.

Regards,