topic Start with set analysis in QlikView

Start with set analysis

Wed, 29 Jan 2014 14:14:57 GMT

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

Re: Start with set analysis

Wed, 29 Jan 2014 14:32:29 GMT

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

Re: Start with set analysis

Michiel_QV_Fan — Wed, 29 Jan 2014 14:33:21 GMT

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.

Re: Start with set analysis

Wed, 29 Jan 2014 14:37:02 GMT

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.

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

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

Have fun

Chris

Re: Start with set analysis

Thu, 30 Jan 2014 13:02:46 GMT

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