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:
- Sheet 1 à with months, days, marker for workingdays.
- 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.
- Sheet 3 à hast the Marker for Product or Batch per Articlegroup for the Budgets
- Sheet 4 à hast the Marker for Fitting Fields per Articlegroup
- 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,
