Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

need for advice about optimization of script and data design

I'm trying to create table with 3 expressions,depended of start and end date(variables, depended by calednar selections). I need to aggredate for the first dimension, to devide for the second one and post text about condition of dimension (I try to use if statement here). I have values for every record and it works fine when I have something selected.It works fine when I have just 1 dimenson and 1 expression in table and nothing in current selection . But my app stoped working when I tried to clear current selections in main table with more then one expressions and more than one dimensions. It can't calculate expressions. It works fine when I my pivot table isn't fully expanded, but it doesn't work on other case. I need to show 2 and more dimensions and two and more expressions at the same table, even I haven't any current selections.What can I do to optimize the expressions?

first expression:

sum(

                              aggr(

if(FirstSortedValue({$<DateOcenka={'<=$(vDate1)'}>} sumOcenka,-Grupa)>0,

                              if(FirstSortedValue({$<DateOcenka={'<=$(vDate1)'}>} sumOcenka,-Grupa)-sum({$<DATASPayment={'<=$(vDate1)'},PaymentType={">79<82"}>} Suma_mbPayment)>0,

                              FirstSortedValue({$<DateOcenka={'<=$(vDate1)'}>} sumOcenka,-Grupa)-sum({$<DATASPayment={'>=$(vMinDate1)<=$(vDate1)'},PaymentType= {">79<82"}>} Suma_mbPayment)

 

                              ,0),0),ClaimID

                              )

          )

the second one:

sum(

                              aggr(if(

                    FirstSortedValue({$<DateOcenka={'<=$(vDate1)'}>} sumOcenka,-Grupa)>0,

          sum({$<DateOfregresiPayment={'<=$(vDate1)'},PaymentTypeReg={">79<82"}>} Suma_mbRegress)

                    ),ClaimID)

 

          ) -

sum(

                              aggr(if(

                    FirstSortedValue({$<DateOcenka={'<=$(vDate1)'}>} sumOcenka,-Grupa)>0,

          sum({$<DATASPayment={'>=$(vMinDate1)<=$(vDate1)'},PaymentType={">79<82"}>} Suma_mbPayment)

                    ),ClaimID)

 

          ) 

=

if(FirstSortedValue({$<DateOcenka={'<=$(vDate1)'}>} sumOcenka,-Grupa)=0,'stage1',

 

          if(FirstSortedValue({$<DateOcenka={'<=$(vDate1)'}>} sumOcenka,-Grupa)-sum({$<DATASPayment={">=$(vMinDate1)<=$(vDate1)"},PaymentType= {">79<82"}>} Suma_mbPayment)>0, 'stage2',

 

                    if(FirstSortedValue({$<DateOcenka={'<=$(vDate1)'}>} sumOcenka,-Grupa)-sum({$<DATASPayment={">=$(vMinDate1)<=$(vDate1)"},PaymentType= {">79<82"}>} Suma_mbPayment)<=0, 'stage3'

                    )

          )

)

. I have attached the database schema for furthermore information.

1 Solution

Accepted Solutions
gaurav2017
Contributor II

Re: need for advice about optimization of script and data design

Hi,

It is for sure that you need to optimise these expressions.

1. By the way, I want to know that: what is the total records from each table?. because i want to know that how much data is there in the qvw.

2. You can use variables for these expressions, in order to trim them.

3. You can make use of joins & applymap function wherever relevant or applicable.

1 Reply
gaurav2017
Contributor II

Re: need for advice about optimization of script and data design

Hi,

It is for sure that you need to optimise these expressions.

1. By the way, I want to know that: what is the total records from each table?. because i want to know that how much data is there in the qvw.

2. You can use variables for these expressions, in order to trim them.

3. You can make use of joins & applymap function wherever relevant or applicable.

Community Browser