Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prabunakil
Contributor III
Contributor III

Help with improving performance of charts that's using a if statement

Hi i have a expression statement like below. It take long time to load when i unselect and select new field. Please help. 

This is only one 

(sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLF2')=1 and match([KSCHL],'Z1PR')=1, [KONV_KWERT]*1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLF2')=1 and match([KSCHL],'Z3PR')=1, [KONV_KWERT]*1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLRE','ZLG3')=1 and match([KSCHL],'Z1PR')=1, [KONV_KWERT]*0))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLS1','ZLG2')=1 and match([KSCHL],'Z1PR')=1, [KONV_KWERT]*-1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLS1')=1 and match([KSCHL],'ZPR0')=1, [KONV_KWERT]*-1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLG2')=1 and match([KSCHL],'ZPR0')=1, [KONV_KWERT]*-1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLS1','ZLG2')=1 and match([KSCHL],'Z3PR')=1, [KONV_KWERT]*-1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'S1','ZLG2','ZLL2','ZLS1')=1 and match([KSCHL],'Z1PR')=1, [KONV_KWERT]*-1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLL2')=1 and match([KSCHL],'ZPR0')=1 and match([AUGRU],'L00')=0, [KONV_KWERT]*1)))-


(sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLRE')=1 and match([KSCHL],'ZPR1')=1, [KONV_KWERT]*1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLL2')=1 and match([KSCHL],'ZPR0')=1 and match ([AUGRU],'L00'), [KONV_KWERT]*-1)))-

 

(sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLF2','ZLL3') and match([KSCHL],'ZHB0'), [KONV_KWERT]*-1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLF2','ZLL3') and match([KSCHL],'ZRBP'), [KONV_KWERT]*-1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLF2','ZLL3') and match([KSCHL],'ZPR0'), [KONV_KWERT]*-1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLF2','ZLL3') and match([KSCHL],'ZHB1'), [KONV_KWERT]*-1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLG3','ZLS1') and match([KSCHL],'ZRBP'), [KONV_KWERT]*1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLG3','ZLS1','S1') and match([KSCHL],'ZPR0'), [KONV_KWERT]*1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLG3','ZLS1') and match([KSCHL],'ZHB0'), [KONV_KWERT]*1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLG3','ZLS1') and match([KSCHL],'ZHB1'), [KONV_KWERT]*1))+

sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLF2')=1 and match([KSCHL],'ZRBA')=1, [KONV_KWERT]*-1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLS1')=1 and match([KSCHL],'ZRBA')=1, [KONV_KWERT]*1))+

sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLF2')=1 and match([KSCHL],'HA00')=1, [KONV_KWERT]*-1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLF2')=1 and match([KSCHL],'ZHB2')=1, [KONV_KWERT]*-1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLS1')=1 and match([KSCHL],'HA00')=1, [KONV_KWERT]*1))+
sum(if([Billing Item]=[Billing Item2] and match([Bill Type],'ZLS1')=1 and match([KSCHL],'ZHB2')=1, [KONV_KWERT]*1)))

Labels (3)
3 Replies
marcus_sommer

I doubt that I would do it within the UI else I would flag it within the script and then a simple:

sum([KONV_KWERT]*[KONV_KWERT_FLAG])

would be enough.

Should it really happens within the UI you should move the conditions from inside the sum() to the outside, like:

if([Billing Item]=[Billing Item2],
   if(match([Bill Type],'ZLF2')=1 and match([KSCHL],'Z1PR')=1, sum([KONV_KWERT]*1),
   ...

Further there are multiple conditions which lead to the same result of multiplying with 1 or -1 and could be consolidated with OR and AND statements or maybe even within your match-queries.

- Marcus

prabunakil
Contributor III
Contributor III
Author

Hi, i am follow ur expression but my figure is no showing .

 

if([Billing Item]=[Billing Item2],
if(match([Bill Type],'ZLRE')=1 and match([KSCHL],'ZPR1')=1, sum([KONV_KWERT]*1))+
if(match([Bill Type],'ZLL2')=1 and match([KSCHL],'ZPR0')=1 and match ([AUGRU],'L00'), sum([KONV_KWERT]*-1))

marcus_sommer

At the end there is a bracket missing (from the first if-condition). Further the result for this shortened expression might be 0 and in such case it will depend on your object-settings if the object showed it or surpressed it.

- Marcus