Expression causing slow performance

[khaycock]

I have this massive expression to work out a standard discount on 4 separate straight tables that is causing me a lot of problems when its being opened in Access Point or if its even opened like normal in QlikView (either doesn't load the table for about 15 minutes or crashes completely). It works fine without the expression so I know that is what is causing the problem but I don't know why its causing these problems.

Is there anything anyone can see in there that might be causing the problem?

=num(

1-(

(if(IsNull([Std Disc%]),1,(1-[Std Disc%]))*(Sum( {<PARTS_TYPE={'Wholegoods'},SHIP_FROM_ORGANIZATION_NAME={'IM','ID'},PD={'1'},DATE_TYPE={'SHIP_DATE'},CM={1},FiscalMonth=,FiscalQuarter=,FiscalYear=>}(UNIT_LIST_PRICE_GBP))))

     /

   Sum( {<PARTS_TYPE={'Wholegoods'},SHIP_FROM_ORGANIZATION_NAME={'IM','ID'},PD={'1'},DATE_TYPE={'SHIP_DATE'},CM={1},FiscalMonth=,FiscalQuarter=,FiscalYear=>}(UNIT_LIST_PRICE_GBP))

    )

,'#.0%')

[vishsaggi]

Not sure why, did you try using your textbox and seperate these two expressions and see what value you are getting? Can you share your sample app if possible ?

[Anonymous]

I can't see anything obvious.  You could try calculating the discount in the load script, it might take a bit longer to load but you should not have any performance issues for the user.

[vinieme12]

Does this expression contain fields from different tables?

Are your selections in the same table or related tables?

Do you have reference to an island table?

can you post a snapshot and highlight fields being used or just specify which table has the fields being used in the above expression?

[marcus_sommer]

The slow response of these expression will probably caused from the used fields within them - if they are from different tables which are connected over multiple associations or even loosen tables it will create huge temporary tables on which the aggregations will be performed. So the first step will be to look and to adjust the datamodel.

Beside them your expression looked like a quote but both sum-parts are the same unless your check on [Std Disc%] - maybe you could find a more efficient way to calculate it.

Further instead:

(if(IsNull([Std Disc%]),1,(1-[Std Disc%]))

you could use:

alt(1-[Std Disc%], 1)

and the condition here:

PD={'1'}

should be a numeric one, like:

PD={1}

and not a string-comparison.

- Marcus