Can't get a simple minus calculation to work

[thedon_1]

I have 2 joined queries, one that gives me figures for a single period, then a % (lets say M1) of one measure over the other. I have then joined via an outer join to another query which is the same thing but for a group of periods.

In the final query (result of the joins) I have a variance of M1 between the 2 queries. The actual % values from each query are right, but the variance just doesn't work.

I'm using this in the data item

Code Select Expand

(if([D1 6 Months] is missing) then (0)
Else([D1 6 Months]))
-
(if([D1 Period] is missing) then (0)
Else([D1 Period]))

It gives me weird numbers. I'm guessing it's because of the aggregation. I've tried loads of combinations but none work.

Can anyone help me please?

[blom0344]

Code Select Expand



total(case when [D1 6 Months] is null then (0) else [D1 6 Months] end) -
total(case when [D1 Period] is null then (0) else [D1 6 Period] end)



Set aggregation type of the dataitem to calculated

[barrysaab]

thedon_1,did it worked for you.Thanks

[thedon_1]

I tried that but it didn't work. I was previously doing my calculation before joining the 2 queries and then bringing the results into the final query.

I changed that so the calculation is done inn the final query and that sorted it out (Aggregation set to calculated). It must have been something to do with the join.


Thanks

[blom0344]

Perform calculations within the same set. Do not expect correct results over multiple sets