1 Reply Latest reply: Mar 10, 2017 8:33 AM by Sunny Talwar RSS

    New with Set Analysis

    Court van de Lisdonk

      Hello,

       

      I have a question related to Set Analysis.

       

      I have created a straight table (see attached jpg-file) with the columns: Date, Actual, Budget, Diff. (Actual – Budget), ActualBatch, BudgetBatch, Diff.(ActualBatch – BudgetBatch), Total(Actual + ActualBatch), Total(Budget + BudgetBatch) and Diff. Total(Budget + BudgetBatch)

       

      Dimension is DateTaskForce (= Column Date)

       

      At the moment I have two filters: Company and Fitting or Mounting. Of course there are also timefilters, these timefilters work correct.

       

       

      Budgets are loaded through an excelfile Taskforce.xls with 5 sheets:

      1. Sheet 1 à with months, days, marker for workingdays.
      2. Sheet 2 à with the budget-values with columns for total budgets and daily budgets, divided in Product- and Batch-Budgets. In the sheet there is also a column for the month and one for the workingdays per month.
      3. Sheet 3 à hast the Marker for Product or Batch per Articlegroup for the Budgets
      4. Sheet 4 à hast the Marker for Fitting Fields per Articlegroup
      5. Sheet 5 à hast the Parts for every Fitting in the Budgets

       

       

       

      The data for the Sales are loaded through a File from our DataWareHouse. I created different fields for the different filters in the script (see attachted worddoc.).

       

      I have made some simple expressions with if-statements (excel looklike) to create the Table with the wished columns and values:

       

      Actual à = if (company = 1, Sum(ECOwert), if (company = 2, Sum(ESBwert), (Sum(ECOwert) + Sum(ESBwert))))

       

      Budget à = if (company = 1, if(MarkerWorkFree = 'A',  (if(Anteil < 1, (PlanECOpT) * Anteil, PlanECOpT)), 0), if (company = 2, if(MarkerWorkFree = 'A', PlanESBpT, 0), if(MarkerWorkFree = 'A', ( (if(Anteil < 1, (PlanECOpT) * Anteil, PlanECOpT)) + PlanESBpT), 0)))

       

      Diff. (Actual – Budget) à  = (if (company = 1, Sum(ECOwert), if (company = 2, Sum(ESBwert), (Sum(ECOwert) + Sum(ESBwert))))) - (if (company = 1, if(MarkerWorkFree = 'A', (if(Anteil < 1, (PlanECOpT) * Anteil, PlanECOpT)), 0), if (company = 2, if(MarkerWorkFree = 'A', PlanESBpT, 0), if(MarkerWorkFree = 'A', ((if(Anteil < 1, (PlanECOpT) * Anteil, PlanECOpT)) + PlanESBpT), 0))))


      Etc.

       

      ** When MarkerWorkFree = ‘A’ it means that it is a working day and only workingdays have a budget

      ** The filter Fittings is divided in three fields, every field has a part of the Product Budget ECO and ESB in the expressions called Anteil. If Anteil <1 it means that one or more fittingfields are select, when not < 1 the total Budget is shown. 

       

      These simple expressions are static.

       

      Question is can I make them flexible through Set Analysis. And if yes how can I do that, my knowledge of Set Analysis is not such that I would be able to solve this by myself.

       

      I hope someone with more experience can help me, for me it’s a learning process

       

      Thanks in regards,