Start with set analysis

[#1]

Dear community,

A few months ago I created an application for monitoring of machine efficiency in our machine shop. I display it as calculated expression of the chart. Right now it works the way, that user have to select specific day otherwise incorrect data are displayed. Now I would like to modify the application to always display corect data and chart for current selection(e.g. for whole week, month etc.). I have checked QV forums and possibilities and the best way which I found are set analysis. I have only basic knowledge and skills of QV right now so I'm afraid it is out of my league and I would really appriciate your help. The most complicated part for me is how to use set analysis together with if conditions.

Current code:

((if(((max(WorkingTimeDay))-(sum(if(DownTime=1,ActivityMinuteDiff,0))))=0,0,((sum(ProductionTime))/((max(WorkingTimeDay))-(sum(if(DownTime=1,ActivityMinuteDiff,0)))))))

*

(if((sum(if(ContractFirstRow=1,PPVReal,0)))=0,0,((sum(if(ContractFirstRow=1,ProductTotal,0)))/(sum(if(ContractFirstRow=1,PPVReal,0))))))

*

(if((sum(if(ContractFirstRow=1,ProductTotal,0)))=0,0,((sum(if(ContractFirstRow=1,Product,0)))/(sum(if(ContractFirstRow=1,ProductTotal,0)))))))

Many thanks in advance for any tips or advises.

Best Regards,

Petr

[#2]

Hi Petr,

here is just an example of what you could do

((if(((max(WorkingTimeDay))-(sum(if(DownTime=1,ActivityMinuteDiff,0))))=0,0,((sum(ProductionTime))/((max(WorkingTimeDay))-(sum(if(DownTime=1,ActivityMinuteDiff,0)))))))

becomes

((if(((max(WorkingTimeDay))-(sum({<DownTime>}={"1"} ActivityMinuteDiff)))=0,0,((sum(ProductionTime))/((max(WorkingTimeDay))-(sum({<DownTime>}={"1"} ActivityMinuteDiff))))))

it's a complex one so it is better to make it step by step.

You can use IF and SET ANALYSS together  (..; or not...up to you)

Best regards

Chris

[Michiel_QV_Fan]

Petr, what can be calculated in the load script? The more you can calculate there, the more clear your expression can be (= less if statements and/or dimensions).

if(((max(WorkingTimeDay))-(sum(if(DownTime can be calculated in the script to 1 dimension, for instance WTDDT

then it would be if(WTDDT = 1, ActivityMInyteDiff, 0) ......

etc. etc.

Also try combining if statements in your load script.

[#4]

Thanks to this website: http://tools.qlikblog.at/SetAnalysisWizard/Set-Analysis-Example--Indirect-Set-Analysis.aspx

VERY USEFUL

http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=_001

Set Analysis Examples - Indirect Set Modifiers/Indirect Set Analysis

These examples demonstrate the advanced technique of indirect sets.

1

Returns the Sum of Sales for the current selection, but only for those customers that have bought the product "Shoe".

(This is one of the examples of the "QlikView Help").

Sum({$<Customer=P({1<Product={'Shoe'}>}Customer)>}Sales)

Open Wizard

2

Return the Sum of Sales for the current selection, but only for those customers who have ever supplied the product "Shoe".

Sum({$<Customer=P({1<Product={'Shoe'}>}Supplier)>}Sales)

Open Wizard

© qlikblog.at / Stefan Walther 2013 ~  "Set Analysis Wizard for QlikView" Version 0.5

Have fun

Chris

[#5]

Ok guys thank you for your answers. I will try to apply your tips and we will see.