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: 
rustyfishbones
Master II
Master II

remove first brackets on concatenated expression

Hi All,

I have the following expression, which I use in the extension called MinimalistHTMLTextBox. This extension acts like a marque and moves the text from left to right on the screen.

='<marquee behavior="scroll" direction="left"><font color="$(color.kpi.headline)">'


&


Concat(DISTINCT If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0, CUSTOMER & ' - ')


& Num(Aggr($(e.charge-amount), CUSTOMER), '£#,##0') &


'<font color="RGB(170,0,0)">' & ' ('  & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')'  & '</font>'



, '<font color="black">' & ' , ' & '</font>' , -Aggr($(e.charge-amount), CUSTOMER))


The result works fine, however I always have blank brackets at the start of the string and I want to remove these


2018-04-18_0932.png

Whats the best way of removing these?

Regards

Alan

1 Solution

Accepted Solutions
sunny_talwar

So, this isn't working (Note the part in blue  which I missed earlier)

='<marquee behavior="scroll" direction="left"><font color="$(color.kpi.headline)">'

&

Concat(DISTINCT If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0, CUSTOMER & ' - ')

& Num(Aggr($(e.charge-amount), CUSTOMER), '£#,##0') &

'<font color="RGB(170,0,0)">' &

If(Len(Trim(Aggr($(e.charge-amount), Customer)))>0,


' ('  & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')'

)

& '</font>'

, '<font color="black">' & ' , ' & '</font>' , -Aggr($(e.charge-amount), CUSTOMER))

View solution in original post

11 Replies
sunny_talwar

May be this

='<marquee behavior="scroll" direction="left"><font color="$(color.kpi.headline)">'

&

Concat(DISTINCT If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0, CUSTOMER & ' - ')

& Num(Aggr($(e.charge-amount), CUSTOMER), '£#,##0') &

'<font color="RGB(170,0,0)">' &

If(Len(Trim(Aggr($(e.charge-amount),

' ('  & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')'

)

& '</font>'

, '<font color="black">' & ' , ' & '</font>' , -Aggr($(e.charge-amount), CUSTOMER))

PrashantSangle

try below,

='<marquee behavior="scroll" direction="left"><font color="$(color.kpi.headline)">'

&

If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0,

Concat(DISTINCT If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0, CUSTOMER & ' - ')

&

Num(Aggr($(e.charge-amount), CUSTOMER), '£#,##0')

&

'<font color="RGB(170,0,0)">' & ' ('  & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')'  & '</font>'

)


or


='<marquee behavior="scroll" direction="left"><font color="$(color.kpi.headline)">'

&

If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0,

Concat(DISTINCT If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0, CUSTOMER & ' - ')

&

If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0,Num(Aggr($(e.charge-amount), CUSTOMER), '£#,##0'))

&

'<font color="RGB(170,0,0)">' & ' ('  & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')'  & '</font>'

)



Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
rustyfishbones
Master II
Master II
Author

Thanks I have tried all 3 and they don't show anything.

PrashantSangle

Is it giving you any error?? like syntax error just try it with out marquee field.

in  stalwar1‌  expression might be required aggregation field.

If(Len(Trim(Aggr($(e.charge-amount),Customer)))>0,

' ('  & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')'

)

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
rustyfishbones
Master II
Master II
Author

Hi,

When I use it outside of the marquee it returns a blank text object.

Regards

Alan

sunny_talwar

So, this isn't working (Note the part in blue  which I missed earlier)

='<marquee behavior="scroll" direction="left"><font color="$(color.kpi.headline)">'

&

Concat(DISTINCT If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0, CUSTOMER & ' - ')

& Num(Aggr($(e.charge-amount), CUSTOMER), '£#,##0') &

'<font color="RGB(170,0,0)">' &

If(Len(Trim(Aggr($(e.charge-amount), Customer)))>0,


' ('  & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')'

)

& '</font>'

, '<font color="black">' & ' , ' & '</font>' , -Aggr($(e.charge-amount), CUSTOMER))

rustyfishbones
Master II
Master II
Author

Yes, that's it, the only issue I have now is it is still showing the first comma.

Any ideas?

2018-04-18_1223.png

sunny_talwar

Try one of these

=If(Len(Trim(Aggr($(e.charge-amount), Customer)))>0,


'<marquee behavior="scroll" direction="left"><font color="$(color.kpi.headline)">'

&

Concat(DISTINCT If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0, CUSTOMER & ' - ')

& Num(Aggr($(e.charge-amount), CUSTOMER), '£#,##0') &

'<font color="RGB(170,0,0)">' &


' ('  & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')'

& '</font>'

, '<font color="black">' & ' , ' & '</font>' , -Aggr($(e.charge-amount), CUSTOMER)))

or

='<marquee behavior="scroll" direction="left"><font color="$(color.kpi.headline)">'

&

Concat(DISTINCT If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0, CUSTOMER & ' - ')

& Num(Aggr($(e.charge-amount), CUSTOMER), '£#,##0') &

'<font color="RGB(170,0,0)">' &

If(Len(Trim(Aggr($(e.charge-amount), Customer)))>0,


' ('  & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')'

)

&


If(Len(Trim(Aggr($(e.charge-amount), Customer)))>0,


'</font>'

, '<font color="black">' & ' , ' & '</font>' , -Aggr($(e.charge-amount), CUSTOMER))))

rustyfishbones
Master II
Master II
Author

GOT IT

='<marquee behavior="scroll" direction="left"><font color="$(color.kpi.headline)">'

&

Concat(DISTINCT If(Len(Trim(Aggr($(e.charge-amount), CUSTOMER))) > 0, CUSTOMER & ' - ')


& Num(Aggr($(e.charge-amount), CUSTOMER), '£#,##0') &


'<font color="RGB(170,0,0)">' &


If(Len(Trim(Aggr($(e.charge-amount),CUSTOMER))) > 0,


' ('  & Num((Aggr($(e.charge-amount), CUSTOMER)/SUM(TOTAL [charge-amount])),'0.00%') & ')'


)


& '</font>'


, '<font color="black">' & If(Len(Trim(Aggr($(e.charge-amount),CUSTOMER))) > 0,' , ' ,' ')& '</font>' , -Aggr($(e.charge-amount), CUSTOMER))