topic Re: Aggr doesn't work when the expression inside is e() in QlikView

Aggr doesn't work when the expression inside is e()

mohamed_ahid — Fri, 23 Jan 2015 13:09:39 GMT

Hi all ,

I'm facing a strange probleme(may be not) .

I'm using an aggr function on a set analysis expression like this way :

aggr( count({<area =e(area)>} bills) , customer) (i need to have the number of bills agregated by customer not living in selected area )

this expression is not working , but when delete the exclusion formula it works .

Any idea plz

Re: Aggr doesn't work when the expression inside is e()

alexandros17 — Fri, 23 Jan 2015 13:32:22 GMT

I sholud do

aggr( Sum({<area={'<>GetFieldSelections(area)'}>} bills) , customer)

Re: Aggr doesn't work when the expression inside is e()

kuba_michalik — Fri, 23 Jan 2015 13:52:38 GMT

I think it goes like this: the set of records for which the count() function is aggregated is specified by the set analysis for the count() function. However, the set of values of dimension (customer, in this case) over which aggr() agregates is not. It's just current selections. So, if you try to count bills from excluded areas over customers from non-excluded areas (current selection, remember!) you get of course zilch.

What you can do, is wrap the aggr() in an outer aggregation function and specify the set analysis again there. Then, aggr will aggregagate the inner aggregation function (taking its set into account) over the dimension values which are valid for the outer function's set expression. If this is confusing, have a look at the attached example

If you are using that aggr() as a calculated dimension, the principle is the same. Make sure the sets for chart expressions are the same as the one for dimension, or at least they have a non-empty intersect.

Re: Aggr doesn't work when the expression inside is e()

mohamed_ahid — Fri, 23 Jan 2015 14:14:58 GMT

thx Alessandro ,

But i don't think that putting the expression simply like area={'<>GetFieldSelections(area)'}>} will works .

i have already faced this kind of situation and i have figured out this way :

dimension =$(if(GetSelectedCount([dimension ], true( ))>0,'{' & chr(34) & replace(GetFieldSelections([dimension ],chr(34) & ',' & chr(34),GetSelectedCount([dimension ], true( ))),chr(39),'*') & chr(34) & '}'),'')

Any way , it's not resolving my problem

Re: Aggr doesn't work when the expression inside is e()

Gysbert_Wassenaar — Fri, 23 Jan 2015 14:24:07 GMT

Where are you using your expression, as a chart expression or a calculated dimension or in a variable?

And did you select at least on area?

Perhaps you can post an example qlikview document that demonstrates the problem and explains what result you expect.

Re: Aggr doesn't work when the expression inside is e()

kuba_michalik — Fri, 23 Jan 2015 14:25:52 GMT

(and if you want me to be more specific, you'd have to either describe the context in more detail (is that aggr a dimension or expression, if it is the expression, what are the dimensions and vice versa), or, preferably, upload a sample qvw.)

Re: Aggr doesn't work when the expression inside is e()

mohamed_ahid — Fri, 23 Jan 2015 14:44:46 GMT

in a chart expression .

i'll try to post an example file .

thx all

Re: Aggr doesn't work when the expression inside is e()

mohamed_ahid — Fri, 23 Jan 2015 14:55:22 GMT

here is the sample .

the aim of my firste question is to represente the number of transaction by client like this :

Number of transaction count of customer

1 12

2 7

3 2

4 300

5 90

6 67

7 83

8 98

9 2

10 83

11-20 21

21-30 92

.91-100 12

Re: Aggr doesn't work when the expression inside is e()

kuba_michalik — Fri, 23 Jan 2015 15:08:07 GMT

I think you have uploaded the example I posted earlier by accident...

As to the desired result, which column is supposed to be this formula: aggr( count({<area =e(area)>} bills) , customer) ?

Re: Aggr doesn't work when the expression inside is e()

mohamed_ahid — Fri, 23 Jan 2015 15:18:17 GMT

yes sorry its the same but i have added my data in .

here is the new sample .

thx

Re: Aggr doesn't work when the expression inside is e()

kuba_michalik — Fri, 23 Jan 2015 15:36:33 GMT

I'm not sure, but it seems like you were trying to use this aggr() expression as dimension. If so, just repeat the set analysis for the actual expression, which I guess was

Count(customer)

so it would become

Count({<area =e(area)>}customer)

and it shoud start working.

From the example you've posted, a much bigger problem will be getting that dimension to look like that (up to 10 separate values, up from 10 ranges)...

Re: Aggr doesn't work when the expression inside is e()

mohamed_ahid — Fri, 23 Jan 2015 15:46:43 GMT

the dimension that you saw in the table it used to produce a liste of number of transactions range (from 1 to 10) .

the expression will calculate the number of customers for every range .