getting the sum of rows in an aggregate

[#1]

Hi there,

I have the following expression to use in a variable:

count(aggr([Startdate Inspection], [MutationID]))

This gives me the same "expression total" result when I create a straight table with MutationID (Supress When Value is Null is ON, due to how my data model is built) as my dimension and count([Startdate Inspection]) as my expression.

However, I need to get the "sum of rows", because sometimes a Mutation has an inspection twice.

I cannot recreate the "sum of rows" in the aggregate expression. I have tried all kinds of stuff. When i start using sum() around my aggregate it gives me a number that is way to high.

Sorry in advance: i cannot share my qvw due to confidentiality reasons...

Anyone with any ideas?

[effinty2112]

Hi Kevin,

Maybe

Count(DISTINCT MutationID)

is all you need.

or  perhaps

Count({$<[Startdate Inspection] = {'*'}>}DISTINCT MutationID)

Good luck

Andrew

[#3]

Hi Andrew,

Thanks for the suggestion with the wildcard character, did not think of that.

However, it is not the solution. Sadly

[swuehl]

Could you post some sample lines of records and your expected result?

It's unclear how I should read "However, I need to get the 'sum of rows', because sometimes a Mutation has an inspection twice"

Maybe

Sum( Aggr(  Count([Startdate Inspection]), [MutationID]))

[pradosh_thakur]

its still unclear but if i understood you correctly

you should try

sum(aggr(count([Startdate Inspection]), distinct [MutationID])) instaed of   count(aggr([Startdate Inspection], [MutationID]))

or may be

Count({1}[Startdate Inspection]) will give you total no of INSPECTION.

if want the sum of rows in the table itself you can use rangesum with above and rowno()

so kindly elaborate your issue .

regards

Pradosh

[#6]

Hi, sorry for not being clear enough.

A sample: 12 is the result I weant (totals on first row)

MutationID	count([Startdate inspection])	sum(aggr(Count([Startdate inspection]) , [MutationID]))
	200	12
MUT000001	1	1
MUT000005	1	1
MUT000008	1	1
MUT000009	1	1
MUT000010	2	2
MUT000011	2	2
MUT000012	1	1
MUT000013	1	1
MUT000014	1	1
MUT000017	1	1
-	190	0

count([Startdate inspection] will give me as Expression total 200, while sum of rows gives 202.

When i set Supress When Value is Null I end up with:

MutationID	count([Startdate inspection])	sum(aggr(Count([Startdate inspection]) , [MutationID]))
	10	12
MUT000001	1	1
MUT000005	1	1
MUT000008	1	1
MUT000009	1	1
MUT000010	2	2
MUT000011	2	2
MUT000012	1	1
MUT000013	1	1
MUT000014	1	1
MUT000017	1	1

count([Startdate inspection] will give me as Expression total 10, while sum of rows gives 12.

When i use the second expression in a variable I end up with a different result! Which is 202

The use of {1} or {$} does not seem to affect my results.

The use of "distinct" gives either an error or 0

PS: the variable is in a Text object: =$(vTile04Measure).

The expression in the variable is without an equal sign: sum(aggr(Count([Startdate inspection]) , [MutationID]))

[swuehl · Accepted solution]

Seems like

sum(aggr(Count([Startdate inspection]) , [MutationID]))

is what you need.

But you may need to add a set expression to filter on MutationID that are not NULL

sum({<MutationID = {"*"}>} aggr(Count({<MutationID = {"*"}>}[Startdate inspection]) , [MutationID]))

[#8]

That last epxression did it! Thanks a lot! This function within an expression will also be extremely helpful to me in future projects