Aggr Max total

[demcug82]

Hi All,

i have a problem to calculate a KPI (text object).

This is the situation:

Dimension:

-Product

-Version

-Exception

I have to calculate the total count of exception (all products) but only in the last version of every product.

For example

Product 1 with version 1 (two exception), version 2 (one exception), version 3 (no exception)

Product 2 with version 1 (no exception), version 2( one exception), version 3 (one exception)

Product 3 with version 1(one exception), version 2( two exception)

The final value should be = 3   (1+2 of the product 2 and product 3)

I have tried with this:

Count(DISTINCT{1<[Version]={'$(=maxstring([Version]))'}>} Exception)

but works only if i select a product because calculate the count of exception of the last version of all product.

Any suggestions?

Thanks

Dem

[jwjackso]

Try the Aggr function:

=Sum(Aggr(Count(DISTINCT{1<[Version]={'$(=maxstring([Version]))'}>} Exception),Product))

[Digvijay_Singh]

May be this -

Count(DISTINCT{1<[Version]={"=$(=Aggr(maxstring([Version]),Product))"}>} Exception)

[demcug82]

Thanks but no works

[demcug82]

Here an example of application where the value correct should be 2.

[sunny_talwar]

May be try this

=Count(DISTINCT Aggr(If(Len(Trim(Exception)) > 0 and Version = MaxString(TOTAL <Product> Version), Exception), Product, Version))

[demcug82]

Hi Sunny,

in detail what do this formula?

Count distinct of what?

Aggr for?

In the example works but not in real project.

[sunny_talwar]

So, I am counting distinct Exception based on two conditions

1) Exception is not null or is not blank

2) Version is the maximum version per Product

If the two condition meets, count the Exception....

May be you don't need DISTINCT? Just this

=Count(Aggr(If(Len(Trim(Exception)) > 0 and Version = MaxString(TOTAL <Product> Version), Exception), Product, Version))

But not sure if you are getting a smaller number or larger number than expected? If it is smaller, then the above should help...

[Digvijay_Singh]

Hi stalwar1

I get confused when to go with set expression and when with 'if', how do you decide which one to use in different situations? Thanks in advance!

[sunny_talwar]

If the evaluation of if is for the whole chart, go with set analysis. If you have to evaluate if based on the chart's dimension, then set analysis won't work (except for few cases) and you will need if with Aggr(). In this case, the OP wanted MaxString per Product, so Set analysis won't work as set analysis will look at maxString(Version) across the table.