topic Re: using Concat function in a calculated dimension in QlikView

using Concat function in a calculated dimension

Anonymous — Sun, 05 Jul 2015 15:10:09 GMT

Hi,

Is it possible to create a calculated dimension for a chart using a concat function?

I tried 2 variants:

1st

='(' & Concat({<Quantity ={"=sum(Quantity)>0"}>}DISTINCT chr(34) & Brands & chr(34), '|') & ')'

2nd

=Concat({<Quantity ={"=sum(Quantity)>0"}>} DISTINCT Chr(39) & Brands & Chr(39),',')

None of them work.

see the file attached.

Thank you in advance,

Larisa

Re: using Concat function in a calculated dimension

antoniotiman — Sun, 05 Jul 2015 15:24:19 GMT

Hi Larisa,

Why don't use Aggr() instead ?

Aggr(If(Sum(Quantity) > 0,Brands),Brands)

Regards,

Antonio

Re: using Concat function in a calculated dimension

marcus_sommer — Sun, 05 Jul 2015 15:28:59 GMT

You need definitely an aggr to build a calculated dimension:

aggr(Concat({<Quantity ={"=sum(Quantity)>0"}>} DISTINCT Brands,','), Brand)

See: Calculated Dimensions

- Marcus

Re: using Concat function in a calculated dimension

Anonymous — Sun, 05 Jul 2015 15:30:09 GMT

Hi,

I hope this can help you.

Best regards.

Re: using Concat function in a calculated dimension

sunny_talwar — Sun, 05 Jul 2015 15:34:30 GMT

What exactly are you trying to do? You want to see a single bar for all the brands where quantity > 0? Is that your requirement?

Best,

Sunny

Re: using Concat function in a calculated dimension

Anonymous — Sun, 05 Jul 2015 15:45:50 GMT

Hi Giuseppe,

Thank you,

But I can't understand why the condition 'sum(Quantity)>0' doesn't work for this dimension. I want to display only those brands in my dimension where the condition 'sum(Quantity)>0' = truth.

Brands 3,4, 7,8 should not be displayed then.

see the file attached.

Re: using Concat function in a calculated dimension

Anonymous — Sun, 05 Jul 2015 15:52:08 GMT

Hi Larisa!

Try with this:

=if(Aggr(sum(Quantity),Brands)>0,Chr(39)&Brands&Chr(39) & ' ' & Aggr(if(sum(Quantity)>0,sum(Quantity),0),Brands),'')

Re: using Concat function in a calculated dimension

sunny_talwar — Sun, 05 Jul 2015 15:59:16 GMT

this??

Dimension: =If(Aggr(Sum(Quantity), Brands)> 0, Brands)

Expression: Sum(Budget)

Uncheck Suppress Zero-Values on Presentation

Check Suppress When Value Is Null

Attaching the qvw for reference.

HTH

Best,

Sunny