Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
demcug82
Contributor III
Contributor III

Aggr Max total

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

11 Replies
jwjackso
Specialist III
Specialist III

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
Contributor III
Contributor III
Author

Thanks but no works

demcug82
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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.