1 Reply Latest reply: May 17, 2013 12:47 PM by Gaurav Malhotra RSS

    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.