Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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 (2)
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