aggr function

[dasaradh]

Hi

i have data like this

LOAD * INLINE [

    cust no, acc no, bal

    1, 1a, 15000

    2, 2a, 50000

    3, 3a, 20000

    4, 4a, 20000

    1, 1b, 12000

    2, 2b, 5000

    5, 5a, 60000

];

i want to count the no of customers who are having bal more than 50000, here i want to group on custno. i want to use in expression level

i have tried this exp but it is not working

=count(aggr(sum(bal)<=50000,[cust no]))

[sunny_talwar]

Use this:

=Sum(Aggr(Count({<[cust no] = {"=Sum($(vVar1)) < $(vVar)"}>}[cust no]), [cust no]))

Where you control vVar1 for different Balances

If(somethingsisTrue, BalanceEUR, BalanceUSD)

[sunny_talwar]

The expression should work as a chart expression as well. Only thing is that if you are using this in a chart where there is only one dimension and that dimension is [cust no], you can simplify the expression to this:

Count({<[cust no] = {"=Sum($(vVar1)) < $(vVar)"}>}[cust no])

HTH

Best,

Sunny

[dasaradh]

hi sunny,

could you please check this

Set analysis in expression