topic Re: Aggr Max total in QlikView

Aggr Max total

demcug82 — Tue, 27 Feb 2018 16:29:36 GMT

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

Re: Aggr Max total

jwjackso — Tue, 27 Feb 2018 16:41:39 GMT

Try the Aggr function:

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

Re: Aggr Max total

Digvijay_Singh — Tue, 27 Feb 2018 16:45:42 GMT

May be this -

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

Re: Aggr Max total

demcug82 — Tue, 27 Feb 2018 16:55:13 GMT

Thanks but no works

Re: Aggr Max total

demcug82 — Wed, 28 Feb 2018 10:09:06 GMT

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

Re: Aggr Max total

sunny_talwar — Wed, 28 Feb 2018 12:17:19 GMT

May be try this

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

Re: Aggr Max total

demcug82 — Wed, 28 Feb 2018 12:57:47 GMT

Hi Sunny,

in detail what do this formula?

Count distinct of what?

Aggr for?

In the example works but not in real project.

Re: Aggr Max total

sunny_talwar — Wed, 28 Feb 2018 13:01:35 GMT

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...

Re: Aggr Max total

Digvijay_Singh — Wed, 28 Feb 2018 14:36:43 GMT

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!

Re: Aggr Max total

sunny_talwar — Wed, 28 Feb 2018 14:39:02 GMT

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.

Re: Aggr Max total

demcug82 — Wed, 28 Feb 2018 15:44:24 GMT

Sunny without distinct i'm passed from 5 to 53.

5 its' more small but 53 too.

however when i select one product and last version the sum go to "Zero" while i expect a value because there are exception.

Re: Aggr Max total

sunny_talwar — Wed, 28 Feb 2018 15:52:35 GMT

Which product have you selected?