topic Re: What-if analysis and nested aggr in QlikView

What-if analysis and nested aggr

Wed, 15 Jan 2014 12:57:15 GMT

Hello,

I'm fairly new as QV developer and I'm trying to create an application whose purpose will be to do simulations with sliders objects.

I get what I want when I do simple computation but when I try to create new expression combining two simple working computation, it doesn't work.

I created a simplified application to explain my problem better.

Sorry if this message is a bit long, but I will try to write a clear introduction.

Data is stored like this :

DimensionProcess	Cost	ProbabilityValue
Step1	ParamA * ParamB	50%
Step1	ParamC	50%
Step2	ParamD	30%
Step3	ParamD	20%
Step3	ParamE * ParamB	20%
Step3	ParamF	20%

Parameters1	CostDefaultValue1
ParamA	5
ParamC	5
ParamD	10
ParamE	0
ParamF	1

Parameters2	CostDefaultValue2
ParamB	3

In this example : Cost of Step1 = Sum(Cost) = ParamA * ParamB + ParamC = 5 * 3 + 5 = 20

I can add parameters (like "_varParamA" and link it to a slider object) and create an expression like this :

Weighted Cost (Simulation) :

AVG(

AGGR(

SUM(CostDefaultValue1

* if(ParamName2='Param A',_varParamA,

if(ParamName2='Param B',_varParamB,

if(ParamName2='Param C',_varParamC,

CostDefaultValue2

)))

) * ProbabilityValue

,DimensionProcess))

This seems to work.

Now, I have another set of data:

DimensionProcess	Time	ProbabilityValue
Step1	ParamG	50%
Step1	ParamH	50%
Step2	ParamI * ParamJ	30%
Step3	ParamK	20%

ParametersTime1	TimeDefaultValue1
ParamG	20
ParamH	1
ParamI	4
ParamK	1

ParametersTime2	TimeDefaultValue2
ParamJ	3

Like previously, I created an expression as this :

Elaspsed Time (Expected) :

SUM(aggr((sum(ProbabilityValue) / sum(total ProbabilityValue))

* sum((if(ParametersTime1='Param 1',_varParamTime1,

if(ParametersTime1='Param G',_varParamTime2,

TimeDefaultValue1

))

* TimeDefaultValue2))

,DimensionProcess))

This work as well.

Now, problem begins.

I want to combine both expressions to make my first expression influenced by the second one. I tried something like this :

AVG(

AGGR(

SUM(CostDefaultValue1

* if(ParamName2='Param A',if([Elaspsed Time (Expected)] > 30

, _varParamA_high

, if([Elaspsed Time (Expected)] > 20

, _varParamA_medium

, _varParamA)),

if(ParamName2='Param B',_varParamB,

if(ParamName2='Param C',_varParamC,

CostDefaultValue2

)))

) * ProbabilityValue

,DimensionProcess))

I think it doesn't work because it nests different "AGGR" expression (AVG(AGGR(SUM(!!!SUM(AGGR(....)))))).

I'm not very comfortable with Aggr() and maybe I'm completely wrong when I used it.

Do you think it's possible to achieve what I'm trying to do ?

Thank you in advance for any hints,

Jean

Re: What-if analysis and nested aggr

Nicole-Smith — Wed, 15 Jan 2014 18:44:51 GMT

I added your other dimension (Param2) into your aggr() calculations and it changed the values; however, I don't know if it's correct or not. So if this doesn't work and you need more help, please post what the values in that column are supposed to be.

AVG(

        AGGR(

              SUM(CostDefaultValue1

                                    * if(ParamName2='Param A',if(/*column(4)*/

                                                                  AGGR(SUM(if(TimeParamName1='Param 1',_varParamTime1,if(TimeParamName1='Param 2',_varParamTime2,TimeDefaultValue1))+ TimeDefaultValue2

                                                                            )

                                                                        ,Level1,Level2,Level3,ParamName2) > 30

                                                                        , _varParamA_high

                                                                  , if(AGGR(SUM(if(TimeParamName1='Param 1',_varParamTime1,if(TimeParamName1='Param 2',_varParamTime2,TimeDefaultValue1))+ TimeDefaultValue2

                                                                            )

                                                                        ,Level1,Level2,Level3,ParamName2) > 20

                                                                        , _varParamA_medium

                                                                  , _varParamA)),

                                      if(ParamName2='Param B',_varParamB,

                                      if(ParamName2='Param C',_varParamC,

                                          CostDefaultValue2

                                          )))

                ) * ProbabilityValue

            ,Level1,Level2,Level3,ParamName2)





)

Re: What-if analysis and nested aggr

gopalopsharma — Wed, 15 Jan 2014 18:51:40 GMT

Hello Farwul,

Thanks for the detailed problem statement.

Can you please explain the purpose behind what are you trying to do?

What is end goal e.g. do you wish to create a chart to show the values?

It will be good to understand the problem better

Re: Re: What-if analysis and nested aggr

Wed, 15 Jan 2014 23:05:32 GMT

Thanks a lot Nicole!

The error was indeed in the aggr() calculations.

I followed your advice and the result is now correct 🙂

I attach to this message the qvw corrected version for information.

Re: Re: What-if analysis and nested aggr

Wed, 15 Jan 2014 23:28:16 GMT

Hello Gopal,

Nicole Smith's answer was right and I was able to achieve what I was looking for.

The purpose of the application is to do simulations on various processes. Each process can be divided into steps and each step can have a cost and a duration which are themselves sums of variables.

The idea is to substitute those variables by parameters that may be modified by user through slider objects for real time simulation.

The end-goal is to create charts or pivot tables that compare initial values VS user's values.