topic Re: Dynamically create the aggr expression in QlikView

Dynamically create the aggr expression

Tue, 31 Mar 2015 13:02:48 GMT

I want to generate my aggr expression dynamically depending on what the user selects

So aggr(sum(net_amount), contract_id) will be the basic

In case they select dim1

It should be aggr(sum(net_amount), contract, dim1)

And if they choose dim1 and dim2

It should be aggr(sum(net_amount), contract, dim1, dim2)

Is this even possible ?

TIA

Re: Dynamically create the aggr expression

stigchel — Tue, 31 Mar 2015 13:29:52 GMT

Yes construct a variable vDim where you have an expression like (with Dim being the field where you select Dim1 or Dim2)

='Contract,'&Concat(Dim,',')

And in your expression use

aggr(sum(net_amount),$(=vDim))

Re: Dynamically create the aggr expression

Tue, 31 Mar 2015 13:45:09 GMT

Well, I want dim1 to appear only when they have selected something from Dim1 and as and when some value is chosen in Dim2 and Dim3 then the expression should be

aggr(sum(net_amount), dim) when nothing is chosen

aggr(sum(net_amount), dim,dim1) when a value in dim1 is chosen

aggr(sum(net_amount), dim,dim1, dim2) when a value in dim1 and dim 2 is chosen

Re: Dynamically create the aggr expression

stigchel — Tue, 31 Mar 2015 13:51:47 GMT

Well you can put any condition in the variable definition right? So, put in vDim something like

=if(GetSelectedCount(dim1)+GetSelectedCount(dim2)=0,'dim',

if(GetSelectedCount(dim1)=1 and GetSelectedCount(dim2)=0,'dim,dim1',

if(GetSelectedCount(dim2)=1,'dim,dim1, dim2'

)))