topic Re: Advice on Data Model in QlikView

Advice on Data Model

Tue, 08 Jan 2013 16:02:20 GMT

Hi All,

Being new to Qlikview, Im hoping you have some advice how to best create a data model for an app comparing actual Transactions amounts to Budget anounts. In my case, Budgets are tracked by month and year, based on "spend" category. An example spreadsheet is attached, as well as a few rows below.

In the same chart (etc), I want to plot actual dollars spent against the budgeted amount, for both Month-Year and Category. I've tried various methods such as concatenation etc to remove Synthetic keys, but I cannot seem to be able to get a total budget number when selecting a specific Month Year.

In this example, selecting Nov-2012 results in a Budget of $300, when in fact total budget for the month is $550. Should I be using Set Analysis in some fashion?

Thanks!

Transactions:

Date	Category	Amount
10/1/2012	IT	$200
10/2/2012	Sales	$250
10/3/2012	Sales	$251
11/2/2012	IT	$175
11/5/2012	Marketing	$250
12/3/2012	IT	$300
12/4/2012	Operations	$400
1/5/2013	IT	$50
1/6/2013	Sales	$55
1/8/2013	Sales	$60
2/6/2013	Marketing	$65
2/6/2013	IT	$70
3/21/2013	Sales	$75

Budget:

Month	Year	Category	BudgetAmount
Oct	2012	IT	$100
Oct	2012	Sales	$50
Oct	2012	Operations	$200
Oct	2012	Marketing	$250
Nov	2012	IT	$100
Nov	2012	Sales	$50
Nov	2012	Operations	$200
Nov	2012	Marketing	$200
Dec	2012	IT	$100
Dec	2012	Sales	$100
Dec	2012	Operations	$250
Dec	2012	Marketing	$100
Jan	2013	IT	$50
Jan	2013	Sales	$200
Jan	2013	Operations	$250
Jan	2013	Marketing	$100
Feb	2013	IT	$50
Feb	2013	Sales	$200
Feb	2013	Operations	$225
Feb	2013	Marketing	$100
Mar	2013	IT	$100
Mar	2013	Sales	$250
Mar	2013	Operations	$225
Mar	2013	Marketing	$210

Re: Advice on Data Model

Michael_Reese — Tue, 08 Jan 2013 16:25:55 GMT

Create one fact table that contains transactions and budgets. It should have a date key . Set budget dates to 1st of the month.

DateKey

Re: Advice on Data Model

christian77 — Tue, 08 Jan 2013 17:05:18 GMT

Hi:

I usually do the same; I integrate objectives in the fact table, but I set objectives to the end of the month date instead, using monthend function.

Re: Advice on Data Model

hic — Tue, 08 Jan 2013 18:51:27 GMT

One single fact table is usually best, just as both answers above suggest. Also, add a flag that has the value 'Actuals' or 'Budget' and use this as dimension.

You can in fact have a mixed granularity in the fact table. See more on:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/26/mixed-granularity

HIC

Re: Advice on Data Model

Tue, 08 Jan 2013 19:43:26 GMT

All, Many thanks for the quick response! I now understand and have updated my script accordingly. I had to set my budget date key to the first of the month,as setting to month end caused issues with budget dates not being associated with my master calendar.