1

Compare aggregation with fixed number

The data model looks like this:

 

 

I have an insight that sums the per-order-line gross profit (not shown, but comes from the Order_Line table), grouped by OrderDate (from the Order table) and FAPA_Code (from the MarketingCodes table). 

 

I would like to compare the result of this aggregation within an insight to the Gross_Profit value in the FAPA_Budget table, for the given date and FAPA code. 

I have tried using the lookup function but was unsuccessful.

I have tried joining FAPA_Budget to MarketingCodes on the FAPA field and FAPA_Budget to Order on OrderDate, but this creates a circular reference. 

Any ideas on how to compare an aggregation to a fixed number within an insight?

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Can you integrate your budget Gross_Profit number into the OrderLine table ( based on FAPA to MarketingCode to StockNumber ) at the schema level?      

    Like 1
      • Steve W
      • Brownells, Inc.
      • Steve_Ware
      • 6 days ago
      • Reported - view

      R. A. Dawson Sr Sorry if I wasn't clear. The sum of orderlines by FAPA code for a given day is what needs to be compared to the fixed budget for that date. I could easily create an MV to save the result of this aggregation by day and FAPA code and merge it with the budget. But, what I'm looking for is a way to compare an aggregation to a fixed number at runtime. 

      Like
    • Ah - I'll probably be muddying the waters instead of clarifying them but one more shot   ;-)    

       

      Would something like this work?  Create the view above - you'll have repeating budget numbers due to cardinality but you can use a MAX ( or MIN  or AVG ) to get the real budget number when you create a formula like this in the insight or business schema so it will calculate at runtime:

      sum(orderamount, groupby(orderdate, fapa))  - max(budgetamount, groupby(Data, fapa))    

       

      A limitation of this - at least as of when I last asked** -  is that the groupby columns need to be 1) in the insight and 2) in the same order as in the formula.      

       

      **https://community.incorta.com/t/35h1j0c/level-based-measures-limitation-question

      Like 1
Like1 Follow
  • 1 Likes
  • 6 days agoLast active
  • 3Replies
  • 28Views
  • 2 Following

Product Announcement

Incorta 2014.2 is now available!