topic Re: Aggr function on Valuelist in QlikView

Aggr function on Valuelist

n1ef5ng1 — Thu, 25 Apr 2013 06:31:39 GMT

Is it possible to do that? I need an aggr function for every valuelist that i mentioned.

The value that I get on a new pivot table and another pivot table with valuelist differ

(sum(Moves)/sum(Total Moves)) * avg(Rate)

why is this so

Re: Aggr function on Valuelist

n1ef5ng1 — Thu, 25 Apr 2013 06:34:48 GMT

aggr function

vaggr((sum(Moves)/ sum(total Moves)) * sum(Rate),Name))

Why it is not working when it comes with valuelist

Re: Aggr function on Valuelist

er_mohit — Thu, 25 Apr 2013 06:37:23 GMT

aggr(((sum(Moves)/ sum(total Moves)) * sum(Rate)),Name)

Re: Aggr function on Valuelist

n1ef5ng1 — Thu, 25 Apr 2013 06:42:07 GMT

if(ValueList(~~~)= ~~~,

aggr(((sum(Crane)/ sum(total Crane)) * sum(Rate)),Name)

is not working

Re: Aggr function on Valuelist

er_mohit — Thu, 25 Apr 2013 06:55:18 GMT

are you tried to use aggr before if??

try to use that aggr before if

aggr(if(----- then codition ),Name)

Re: Aggr function on Valuelist

n1ef5ng1 — Thu, 25 Apr 2013 07:00:34 GMT

will be using, but till now. the coding I have has only one aggr

Re: Aggr function on Valuelist

kuba_michalik — Thu, 25 Apr 2013 07:21:59 GMT

Hard to say without knowing what dimensions the pivot chart has, and the exact expression you are using.

But one possible thing is that if the chart does not have [Name] as dimension, then aggr returns mutiple values for each cell and that can't be displayed. You'd have to somehow conver what aggr returns to a single value (with an external aggregation function).

Re: Aggr function on Valuelist

Thu, 25 Apr 2013 07:44:33 GMT

try this it working fine,

and see attachement.

test:

LOAD * INLINE [

Name, Rate, Moves

a, 5, 200

b, 10, 300

c, 5, 400

d, 15, 500

e, 20, 200

f, 5, 300

g, 15, 100

];

take Dimension is Name

expression = > aggr((sum(Moves)/ sum(total Moves)) * sum(Rate),Name)

Re: Aggr function on Valuelist

n1ef5ng1 — Thu, 25 Apr 2013 07:47:39 GMT

Thanks for the effort,

however, each of my A B C D E F on your example.... is different formula from different table..

therefore i doubt i can use this method...

Re: Aggr function on Valuelist

marcus_sommer — Thu, 25 Apr 2013 12:01:16 GMT

Generally complexe aggregations on synthetic dimensions like valuelist(), class() ... does not work.

- Marcus