4 Replies Latest reply: Jan 8, 2013 2:43 PM by Dan Shannon RSS

    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