topic Re: Question about sum(total<>) in QlikView

Question about sum(total<>)

salezian — Wed, 14 Mar 2018 09:18:15 GMT

Dear Qlik fans,

Is there any way to include in total keyword all columns in the chart except two specific. The idea is to include always in the expression all columns in the chart except two specific fields like sum(total<all columns - {column1, column2}>) ? Of course I could write all columns manually but this is ad hoc chart and it would be difficult to manage it.

I'd be grateful for any ideas

Re: Question about sum(total<>)

niclaz79 — Wed, 14 Mar 2018 09:39:36 GMT

Hi,

You say it's an ad hoc chart, does that mean you have a separate selection on which are the columns in the table?

If so, you can create a variable containing the selected columns using the Concatenate() function and then use that in the <$(vConcatenatedDimensions)> in your expression.

Re: Question about sum(total<>)

salezian — Wed, 14 Mar 2018 10:03:50 GMT

I've tried but it doesn't work .... concat(_field,',')

Re: Question about sum(total<>)

PrashantSangle — Wed, 14 Mar 2018 11:02:17 GMT

try with getfieldSelections(fieldName,',')

Regards

Re: Question about sum(total<>)

sunny_talwar — Wed, 14 Mar 2018 11:14:09 GMT

Is your list of dimension dynamic in anyway? I mean are you just looking to get a nicer looking expression or are you trying to make it dynamic so that if dimension change, the expression is able to change automatically to incorporate changes in dimensions?

Re: Question about sum(total<>)

salezian — Wed, 14 Mar 2018 11:23:55 GMT

Dear all

It's ad-hoc table in which I calculate ratio. It's important to me to calculate correctly denominator.

It should calculate denominator for all dimensions except the 2 dimensions representing categories.

Re: Question about sum(total<>)

sunny_talwar — Wed, 14 Mar 2018 11:25:15 GMT

How do you select the dimensions for this ad-hoc chart?

Re: Question about sum(total<>)

salezian — Wed, 14 Mar 2018 11:30:19 GMT

There's data island on which user select dimensions and based on this dimensions are conditionally displayed in chart. So I use following condition to display the field

=SubStringCount(Concat(_AdHocTableCode,','),'A10')=1

I've tried to use concat(_AdHocTableField,',') and put in total, but it doesn't work. And I don't understand why.

Re: Question about sum(total<>)

sunny_talwar — Wed, 14 Mar 2018 11:33:47 GMT

So, here is how you can select the dimensions for total qualifier

TOTAL <$(=Concat(DISTINCT {<_AdHocTableCode -= {'Field1', 'Field2'}>} '[' & _AdHocTableCode & ']', ','))>

Here I am assuming that you will exclude Field1 and Field2 from your total qualifier.... replace them accordingly

Re: Question about sum(total<>)

salezian — Wed, 14 Mar 2018 11:46:22 GMT

Yes this it ! Thanks man.

The only thing I don't understand is why expression total<concat(_AdHocTableCode,',')>

didn't work.

Re: Question about sum(total<>)

sunny_talwar — Wed, 14 Mar 2018 11:54:15 GMT

Because you need $() expansion to let QlikView know that the list that is displayed is actual field names and not just any random list of names....Might be able to read some of it here

The Magic of Dollar Expansions

Re: Question about sum(total<>)

salezian — Thu, 15 Mar 2018 07:32:12 GMT

I've used by creating variable with following expression

vTotal: =Concat(DISTINCT {<_AdHocTableCode -= {'Field1', 'Field2'}>} AdHocTableCode )

Then I've used in chart as $(vTotal). And my understanding that this expression will return column names.

That's why I'm confused.

Re: Question about sum(total<>)

sunny_talwar — Thu, 15 Mar 2018 08:47:02 GMT

Try one of these

$(=vTotal)

$(=$(vTotal))