Calculated Field Question

[bs0d]

Assume I have a table of data with many different regions that have many groups.

How can I create a totals query the sum of the result of a calculation by region? For instance, assume I need to perform this calculation, using group ID's:

(25+37+56)-(21+15)

I can limit the query to only pull the group ID's above; but how would I prepare the field that performs that calculation? Also -- this is in a crosstab query.

 

[bs0d]

Here's a stab at it, which doesn't work:

NET_VAL: SUM(IIF([Table].[ID]=25 or [Table].[ID]=37 or [Table].[ID]=56, [Table].[Net_Value], 0))-SUM(IIF([Table].[ID]=21 or [Table].[ID]=15, [Table].[Net_Value], 0))

 

[xenou]

I tend to favor solutions that are derived directly from set theory so this would be an answer as to how to write a query:

select sum(t3.Net_Value)
from
	(
		select 
			sum(Net_Value) 
		from [Table] t1
		where 
			t1.ID in (25,37,56)
		and t1.Net_Value is not null

		union all

		select 
			-(sum(Net_Value)) 
		from [Table] t2
		where 
			t2.ID in (15,21)
		and t2.Net_Value is not null
	)
	as t3

But using IIF() formulas is probably something a lot of people would goto first, so here's another possibly solution (which is untested):

		select 
			sum(iif(Net_Value in (15,21), -Net_Value, Net_Value)) 
		from [Table] t1
		where 
			t1.ID in (15,21,25,37,56)
		and t1.Net_Value is not null

I don't really know where the crosstab part fits in without more information. Usually with crosstabs it is best not to do calculations - resolve all that first and then create the crosstab as an end result based on the earlier query results.

Also my second version looks in principal the same as your try (except for the precaution of excluding Null from sum() which causes the entire result to become Null). So you probably do need to provide more details for proper debugging.

 

[bs0d]

Thanks for the feedback. It'll be a few days before I can test, but ill study your response and let you know how it turns out or get more details as necessary.

 

[bs0d]

I tend to favor solutions that are derived directly from set theory so this would be an answer as to how to write a query:

select sum(t3.Net_Value)
from
	(
		select 
			sum(Net_Value) 
		from [Table] t1
		where 
			t1.ID in (25,37,56)
		and t1.Net_Value is not null

		union all

		select 
			-(sum(Net_Value)) 
		from [Table] t2
		where 
			t2.ID in (15,21)
		and t2.Net_Value is not null
	)
	as t3

But using IIF() formulas is probably something a lot of people would goto first, so here's another possibly solution (which is untested):

		select 
			sum(iif(Net_Value in (15,21), -Net_Value, Net_Value)) 
		from [Table] t1
		where 
			t1.ID in (15,21,25,37,56)
		and t1.Net_Value is not null

I don't really know where the crosstab part fits in without more information. Usually with crosstabs it is best not to do calculations - resolve all that first and then create the crosstab as an end result based on the earlier query results.

Also my second version looks in principal the same as your try (except for the precaution of excluding Null from sum() which causes the entire result to become Null). So you probably do need to provide more details for proper debugging.

I got this to work using the IIF method. However, one revision to your code:

sum(iif(Net_Value in (15,21), -Net_Value, Net_Value))

to:

sum(iif(ID in (15,21), -Net_Value, Net_Value))

I appreciate the help with this.