 The formula col refers a field in a Qry..pl help [message #259978] |
Fri, 17 August 2007 01:29  |
suma_orkl
Messages: 16
Registered: July 2007
|
Junior Member |
|
|
Hi all...
Im trying to refer to two columns in different queries inside a formula column. Its giving an error "the referd column is at a frequency below the group"
My requirement is like this
1) fst Query returns the sales during this week (total1)
2)second qry returns the sales last weel(total2)
3) A formula column to find the variance of sales (total1-total2/total2)%
Is it not possible in any way to handle this situation
please help me..its very urgent.......!
-Suma
|
|
|
|
|
|
|
|
| Re: The formula col refers a field in a Qry..pl help [message #260001 is a reply to message #259978] |
Fri, 17 August 2007 02:20  |
suma_orkl
Messages: 16
Registered: July 2007
|
Junior Member |
|
|
I tried this but the values its giving are not correct since the sales values have to come aftr two filters..i.e
the sales hve to come like this
[U]GROUP DIVISION SALES [/U
marks --> glass ware --> 1852
--> aldo --> 1852
wen I used the summary col outside it gave result like this
[U]GROUP DIVISION SALES [/U]
marks -->glassware --> 3704
--> aldo --> 3704
but The value shud not get summed up.....
so in this case we cannot use the sumary cols outside as it wil give wrong results..
so any other way to do this????
--Suma
|
|
|
|
| Re: The formula col refers a field in a Qry..pl help [message #260004 is a reply to message #260001] |
Fri, 17 August 2007 02:28 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Well, this situation is different from your explanation.
When you said "TOTAL", I read it as "OK, he used a SUM function to compute total". What do you call "total", then?
Are those "1852" values always the same? If so, use FIRST or LAST or AVG instead of SUM function. If not, well, say something more about the problem, possibly with an example. Also, enclose such things into the [code] tags to improve readability.
|
|
|
|
| Re: The formula col refers a field in a Qry..pl help [message #260040 is a reply to message #259978] |
Fri, 17 August 2007 04:24 |
suma_orkl
Messages: 16
Registered: July 2007
|
Junior Member |
|
|
Sorry .. My explaination was wrong as yu said.
the thing is the Total sales has to come thru two filters..
there are several groups and inside groups there are many departments and we have to compute sales for wach and every dept within a group
heirarchy: GROUP1 ---> DEP1,DEP2,DEP3
GROUP2 ---> DEP2,DEP3
Group4---->DEP1,DEPP4
sales Lastweek sales Variance
GROUP1 ---> DEP1 1876
---> DEP2 200
----> DEP3 331.52
Report layout as explained above.
now ..my query is written such that it fetches the rows filtering the Group and department and it repeats itself.
Now If is use a summary col for the qry it gives the total sales belonging to GROUP1 and not GROUP1-->DEP1
I cannt use the figure to calulate variance with the last week same group same dept sales
|
|
|
|
| Re: The formula col refers a field in a Qry..pl help [message #260046 is a reply to message #260040] |
Fri, 17 August 2007 04:40 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I told you the principle, the way I'd try to do that. As I don't have access to your data nor the report you've written, I'm afraid this is the best I can do. Unfortunately, I can not write a report for you based on nothing.
If you can, provide sample data (including CREATE TABLE and INSERT INTO statements, as well as desired output), attach a report and - hopefully - someone will take a look at it.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
