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

# New with Set Analysis

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.

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,

• ###### Re: New with Set Analysis

Would you be able to share a sample where we can play around? Also, it would be helpful to know what is the desired output you are looking to get based on few different set of selections