Subtract a Calculated Field with a Query

[NikkoF]

Hello there.Its Great to meet you all.



For the last few days i am trying to build a pretty difficult expression.It about a company that needs to calculate, round and subtract some values.
The problem is that we need to calculate a Weighted Average of the last 3 years ,round that up or down to the nearest 1 value(no deciminals) and then subtract this expression from a standart fixed field (Quantity).

Here is the catch...As you ll see below at the SYNTAX statement this should bring up the correct result.Instead i have ''SOME'' wrong resaults...meaning that in some rows the weighted average and round functions works just fine but ''IT'' fails to make the correct subtraction from the fixed field.

Sales2011:5
Sales2012:2
Sales2013:1
Q:8

Step1:Giving special weight 1 for Sales2011,2 for Sales2012 and 4 for Sales2013 wich will result around 1.85
WeightAverage: (5*1)+(2*2)+(1*4))/7

Step2:Next is to Round the previous Calculation to the nearest 1 Value(No deciminals) wich will calculate into to 2
Round(1*[CalculatedField],0)/1

Step3:Till this Step everything goes according to plan.Problem comes with the Last Step where i Need to subtract all the above from a standart fixed field.
[Expression]-[Q]

This along with some other rows brings up -4 where the correct formula should reuslt 2-8= -6

IF I TRNSFORM THE WEIGHTED AVERAGE WITH ROUND FUNCTION INTO A TABLE AND THEN RUN THE DIFFRENCE, IT WORKS JUST FINE!!!!!
OUT OF 86 REFFERENCES AROUND 10 OF THEM ARE WRONG.

Expr1: (Round(1*(((([Table].[Sales2011]*1)+([Table].[Sales2012]*2)+([Table].[sales2013]*4))/7))/1))-([Table].[Q])


Thank you in advance for the Help!!!

[ItsMe]

Not tested but this is how I interpret your needs.

Giving special weight 1 for Sales2011,2 for Sales2012 and 4 for Sales2013 wich will result around 1.85
WeightAverage(5*1)+(2*2)+(1*4))/7

Expr1: (([Table].[Sales2011] + ([Table].[Sales2012] * 2) + ([Table].[sales2013] * 4)) / 7)

Next is to Round the previous Calculation to the nearest 1 Value(No deciminals) wich will calculate into to 2
Round(1*[CalculatedField],0)/1

Not sure exactly what this means. The following will round to the whole.
Expr1: Round((([Table].[Sales2011] + ([Table].[Sales2012] * 2) + ([Table].[sales2013] * 4)) / 7), 0)

Till this Step everything goes according to plan.Problem comes with the Last Step where i Need to subtract all the above from a standart fixed field.
[Expression]-[Q]

Subtracting the sum from Q
Expr1:[Table].[Q] - Round((([Table].[Sales2011] + ([Table].[Sales2012] * 2) + ([Table].[sales2013] * 4)) / 7), 0)

Having said all of that, you need to make sure there is a value of at least 0 and not any Nulls in any of the Sales fields or Q field in order to get a sum.

[NikkoF]

Sales2011 5
Sales2012 2
Sales2013 1
Q 8


This Should result ((5*1)+(2*2)+(2*4))/7 = 1.85714286

Rounding this will result Round(1*1.85714286,0)/1 = 2

Till this step all my results are correct!

Now i need to subtract the previous statement from Q.

Till this step both my approach and yours gives the same correct results.The challenge comes when i need subtract this expression from Q.Then i have ''some errors''(2-8=-4 instead of -6) and this is where i bust my brains.If the approach was incorrect ,all of the records would be wrong!Most of the results are correct but some at least 10,didnt count exactly how much, are NOT.Cant figure out why.

If i MakeTable with an expression on first 2 steps (WeightAverage and Round =2) and then subtrack from Q with a query([NewField]-[Q])....ALL THE RECORDS ARE CORRECT.

I have NO less than 0 or Nulls in the data used!!!All of the data are =>0

[ItsMe]

Correct, you need to figure out what you are subtracting from.

My example shows subtracting the sum from the Q field.

Also, I do not understand the purpose of
(5*1)
or
(1*1.85714286,0)/1

So refactored to
(5)
and
(1.85714286,0)

[NikkoF]

I need to calculate the average unit sold in the last 3 years.Instead of just taking a simple Average i need to give greater significance at the last years sales(=Sales2013) and less in the 2011 s(Sales2011) This is why i figured that WeightedAverage would be a great implemetation....

I am trying to figure out any syntax mistakes with the expression...but i cant spot one.The expression should work just fine....BUT it doesnt.At least not completly!I have tried a lot of variations in order to spot where exaclty i am facing the problem....

It all come down to subtracting the above expression from a Fixed Field (Q).When it comes to this it just doesnt work!

P.S. You are correct.I just used this formula (5*1,etc.) in order to show the logic behind what i am trying to do.

[ItsMe]

...It all come down to subtracting the above expression from a Fixed Field (Q)....

Then this should work

Expr1: [Table].[Q] - Round((([Table].[Sales2011] + ([Table].[Sales2012] * 2) + ([Table].[sales2013] * 4)) / 7), 0)

Otherwise, maybe this.

Expr1: Round((([Table].[Sales2011] + ([Table].[Sales2012] * 2) + ([Table].[sales2013] * 4)) / 7), 0) - [Table].[Q]

When I looked at your original code, the parenthesis needed some attention. If you have to, place the first part of the expression in a separate query object as an alias with a meaningful name.

MyAvg: Round((([Table].[Sales2011] + ([Table].[Sales2012] * 2) + ([Table].[sales2013] * 4)) / 7), 0)

Then, in the original query, bring your subquery in on a join and do your subtraction using the alias.
expr1: [SubqueryName].[MyAvg] - [Table].[Q]
or
expr1: [Table].[Q] - [SubqueryName].[MyAvg]

[NikkoF]

I have retested your approach ItsMe....but unfortunately...my results are still wrong!!!!!!!

expr1: [SubqueryName].[MyAvg] - [Table].[Q]

this translates into
2 - 8 = -4 instead of -6....
or some other ones
6 - 8 = 0 instead of -2....
0 - 5 = -3 instead of -5.....
1 - 2 = 0 instead of -1...

This is a pure Headache!!!!

Really cant think of anything else....going wrong...

[ItsMe]

I do not see a pattern in the results you have posted. Because of this, I believe there is a variable you are not considering. In other words, computers do not produce random results. You have to program random.

If you like, you can upload your DB and I will try to set time aside to take a look at it or someone else may jump in and take a look. Just remove private data and include enough data to run the queries. Zip down the file before uploading.

[NikkoF]

I have exported the file into a new DB.Before i upload it in order for you to take a look at it...i run again the Expression above.It worked properly!!!!

As i told you before...the caculation is correct if i export the Query to a new Table.

Any thoughts...?

[ItsMe]

...the caculation is correct if i export the Query to a new Table...

I am having a hard time understanding what this means, so it does not make sense. View the data a query retrieves should not produce different results than exporting the data a query retrieves. Perhaps you are trying to run an aggregate function with another aggregate function simultaneously.

[hapm]

I would be interested in the full query. If it is a simple query on only one table, then what you describe shouldn't happen at all, or I missed something in your description of the problem.

[Geo21]

Can you upload the file?

[NikkoF]

I am having a hard time understanding what this means, so it does not make sense. View the data a query retrieves should not produce different results than exporting the data a query retrieves. Perhaps you are trying to run an aggregate function with another aggregate function simultaneously.

No it is based on 2 Different tables Tables....