Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Advice on Data Model

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:

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

Budget:

MonthYearCategoryBudgetAmount
Oct2012IT$100
Oct2012Sales$50
Oct2012Operations$200
Oct2012Marketing$250
Nov2012IT$100
Nov2012Sales$50
Nov2012Operations$200
Nov2012Marketing$200
Dec2012IT$100
Dec2012Sales$100
Dec2012Operations$250
Dec2012Marketing$100
Jan2013IT$50
Jan2013Sales$200
Jan2013Operations$250
Jan2013Marketing$100
Feb2013IT$50
Feb2013Sales$200
Feb2013Operations$225
Feb2013Marketing$100
Mar2013IT$100
Mar2013Sales$250
Mar2013Operations$225
Mar2013Marketing$210
1 Solution

Accepted Solutions
Highlighted
Employee
Employee

Re: Advice on Data Model

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

DateKey

Category

BudgetAmount

Amount

View solution in original post

4 Replies
Highlighted
Employee
Employee

Re: Advice on Data Model

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

DateKey

Category

BudgetAmount

Amount

View solution in original post

Partner
Partner

Re: Advice on Data Model

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

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

Not applicable

Re: Advice on Data Model

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.