need for advice about optimization of script and data design

Fri, 17 May 2013 12:05:37 GMT

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.

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

gaurav2017 — Fri, 17 May 2013 16:47:55 GMT

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.

topic need for advice about optimization of script and data design in QlikView

need for advice about optimization of script and data design

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