topic Re: AGGR Expression issue in QlikView

AGGR Expression issue

master_student — Tue, 12 Jul 2016 08:26:38 GMT

Hello everyone,

I am using an expression with AGGR function :

Sum (Aggr( Sum({< $(vSetView)>} POKTND) , LB,Famille,CODE_PROJET,PO_FOURNISSEUR,PO_NUM_COMMANDE,PO_COMMENTS, PO_ACHETEUR,[Month of date], CDR) )

but the problem is that I am using $(vSetView) to show MTD /YTD / Total YEAR data , so that the data change with the selection :

$(vSetView)=pick(ViewId,

'$(vSetMTD)',

'$(vSetYTD)',

'$(vSetTotal)')

View:

LOAD * INLINE [

ViewId, View

1, MTD

2, YTD

3, Total Year

];

any help so that my data change with the selection please?

Thanks

pb with the total displayed

Re: AGGR Expression issue

master_student — Tue, 12 Jul 2016 09:34:15 GMT

Any idea please...

Re: AGGR Expression issue

inescastelhano — Tue, 12 Jul 2016 13:27:41 GMT

Hi,

You are not evaluating your expression... Look at your variable:

Sum (Aggr( Sum({< $(vSetView)>}

You are supposed to have something like Date = $(myvariable) ....

Cheers,

Ines

Re: AGGR Expression issue

master_student — Tue, 12 Jul 2016 13:41:57 GMT

Thanks Ines, could explain more please

Re: AGGR Expression issue

Anonymous — Tue, 12 Jul 2016 13:52:49 GMT

Hi,

Try replacing $(vSetView) with $(=$(vSetView))

Maurice

Re: AGGR Expression issue

master_student — Tue, 12 Jul 2016 13:59:38 GMT

No it didn't work Maurice

Sum (Aggr( Sum($(=$(vSetView)) POKTND) , LB,Famille,CODE_PROJET,PO_FOURNISSEUR,PO_NUM_COMMANDE,PO_COMMENTS, PO_ACHETEUR, CDR) )

Re: AGGR Expression issue

Anonymous — Tue, 12 Jul 2016 14:05:33 GMT

You need to adapt your script like my below example that works very well:

View:

LOAD * INLINE [

ViewId, View

1, 2008

2, 2009

];

SET vSet2008='Year={2008}';

SET vSet2009='Year={2009}';

SET vSetView=pick(ViewId, vSet2008, vSet2009);

Then use expression like: sum({< $(=$(vSetView)) >} Sales)

Re: AGGR Expression issue

master_student — Wed, 13 Jul 2016 08:07:51 GMT

sum({< $(=$(vSetView)) >} Sales) works very well but whene I use AGGR FUNCTION it doesn't work; the amount doesn't change with the selection.

Sum (Aggr( Sum($(=$(vSetView)) POKTND) , LB,Famille,CODE_PROJET,PO_FOURNISSEUR,PO_NUM_COMMANDE,PO_COMMENTS, PO_ACHETEUR, CDR) )

Re: AGGR Expression issue

Anonymous — Wed, 13 Jul 2016 09:09:43 GMT

Hi Bekir,

In my example if i use same template as you:

=sum(aggr(sum({< $(=$(vSetView)) >} Sales), CategoryName))

It also works well...

So i think you have something wrong maybe with the fields you are using.

Be sure also to put = sign to start the expression as sum is a function.

Maurice

Re: AGGR Expression issue

master_student — Wed, 13 Jul 2016 10:03:42 GMT

As recommended, see below

=sum(aggr(sum({< $(=$(vSetView)) >} POKTND), LB,Famille,CODE_PROJET,PO_FOURNISSEUR,PO_NUM_COMMANDE,PO_COMMENTS, PO_ACHETEUR, CDR) )

Bellow are my variable expressions :

'$(vSetMTD)', : [Calendar date]= , [Month of date]={ "= $(=max([Month of date]))"}, [%Date ID] = { "<= $(=max([%Date ID]))"}

'$(vSetYTD)', : [Year of date]={ $(=[Year of date]) } , [Month of date]={ "<= $(=max([Month of date]))"} , [%Date ID] = { "<= $(=max([%Date ID]))"}

'$(vSetTotal)') : [Calendar date]=, [Month of date]=, [Year of date]={ $(=[Year of date]) }

$(vSetView)=pick(ViewId,

'$(vSetMTD)',

'$(vSetYTD)',

'$(vSetTotal)')

The amount remains the same with all selections MTD/YTD and total year...

did I miss something?

Thanks

Re: AGGR Expression issue

Anonymous — Wed, 13 Jul 2016 11:29:59 GMT

Try using variables in pick function without the $ and quotes:

SET vSetView=pick(ViewId, vSetMTD, vSetYTD, vSetTotal);

And create your variables surrounded by simple quotes

Start with simple and static one like:

SET vSetYTD='[Year of date]={'2016'}';

If this is working then you can adapt progressively the variables complexity.