topic Re: Use Alternate state in expression with aggr in QlikView

Use Alternate state in expression with aggr

Anonymous — Thu, 18 Jun 2015 06:53:40 GMT

Dear Community,

i have following expression:

SUM(aggr(DISTINCT(AMOUNT),SN,DATE,AMOUNT))

And the alternate state:

Now i want to transform this expression so it runs under the alternate state X

I tried :

sum({X} aggr(DISTINCT(AMOUNT),SN,DATE,AMOUNT))

But the aggreagate is not working over this alternate state

Please help!

Re: Use Alternate state in expression with aggr

avinashelite — Thu, 18 Jun 2015 06:58:44 GMT

no , you should mention the dimension in alternate state to act on this count , try like this

sum({X<D1 = $::D1,D2 = $::D2} aggr(DISTINCT(AMOUNT),SN,DATE,AMOUNT))

here the D1 and D2 are the dimension which are being used in the alternate state

Re: Use Alternate state in expression with aggr

ecolomer — Thu, 18 Jun 2015 07:01:00 GMT

try this

sum(aggr({X} Distinct (AMOUNT), SN,DATE, AMOUNT))

and in the alternate state dont select X

Re: Use Alternate state in expression with aggr

Anonymous — Thu, 18 Jun 2015 07:07:21 GMT

Hi Enrique,

With such a syntax i am getting the same result as the initial expression

SUM(aggr(DISTINCT(AMOUNT),SN,DATE,AMOUNT)) so it does not use the alternate state

Re: Use Alternate state in expression with aggr

Anonymous — Thu, 18 Jun 2015 07:17:30 GMT

Hi Avinash,

i mentioned now all dimensions :

sum({X<DATE = $::DATE,SN = $::SN,AMOUNT= $::AMOUNT} aggr(DISTINCT(AMOUNT),SN,DATE,AMOUNT))

but its still not working i am getting all the time the same result as the initial expression:

SUM(aggr(DISTINCT(AMOUNT),SN,DATE,AMOUNT))

When using simple:

sum({X}AMOUNT) its working but i need the aggregate!

Re: Use Alternate state in expression with aggr

sasiparupudi1 — Thu, 18 Jun 2015 07:51:10 GMT

Please post your application here

Sasi

Re: Use Alternate state in expression with aggr

antoniotiman — Thu, 18 Jun 2015 08:07:19 GMT

sum(aggr(DISTINCT Only({X} AMOUNT),SN,DATE,AMOUNT))

or Sum( instead Only(

Re: Use Alternate state in expression with aggr

avinashelite — Thu, 18 Jun 2015 09:39:35 GMT

hi BioB_Paul,

As per knowledge we cannot specify the aggr function to aggregate on the alternate state dimensions, by default it will on the default state, if you want to aggregate then your chart and the aggregating dimension need to be in same state...no other way yo get it work

Re: Use Alternate state in expression with aggr

sasiparupudi1 — Thu, 18 Jun 2015 10:25:52 GMT

Check this post Set Analysis with Aggr and Alternate States

Re: Use Alternate state in expression with aggr

Anonymous — Thu, 18 Jun 2015 12:10:16 GMT

Still it does not work, and behaves like its not using the alternate state for the aggr.

Tried "only" or "sum" ...

Re: Use Alternate state in expression with aggr

Anonymous — Thu, 18 Jun 2015 12:28:43 GMT

Does not help me, if i switch the alternate state of the object to the desired one it works.

But the state of the object needs to stay untouchd, the alternate state needs be passed to the aggregate, thats what i am struggeling with

Re: Use Alternate state in expression with aggr

avinashelite — Thu, 18 Jun 2015 12:40:24 GMT

Hi,

Every tool has certain limitations and similarly even with Qlikview. This is one of the cases

Re: Use Alternate state in expression with aggr

kuba_michalik — Thu, 18 Jun 2015 13:32:21 GMT

I'm not sure if it's possible to look at it properly without an example qvw.

I whipped up a quick test case and it seems to work, whether you stick the alternate state set expression in the outer or in the inner aggregation function (so I put it in both )

Re: Use Alternate state in expression with aggr

sasiparupudi1 — Fri, 26 Jun 2015 06:51:19 GMT

Please post your qvw here