New with Set Analysis

pacoli2013 — Fri, 10 Mar 2017 13:29:46 GMT

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,

Re: New with Set Analysis

sunny_talwar — Fri, 10 Mar 2017 13:33:36 GMT

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

topic New with Set Analysis in QlikView

New with Set Analysis

Re: New with Set Analysis