Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

pacoli2013
Contributor

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.

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,

 

1 Reply

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

Community Browser