Calculation in View

cheongkabo
cheongkabo used Ask the Experts™
on
Form :
Sales Person : [field1]          Date : [field2]
Total Sales : [field3]

field1 - editable(text)
field2 - computed(@Created, Time)
field3 - editable(text)


Sample Doc1 :

Sales Person : Andrew          Date : 11/02/2002
Total Sales : 200.00

Sample Doc2 :
Sales Person : Andrew          Date : 11/05/2002
Total Sales : 150.00


Sample Doc3 :
Sales Person : Brian          Date : 11/02/2002
Total Sales : 130.00


Sample Doc4 :
Sales Person : Brian          Date : 11/06/2002
Total Sales : 30.00


View :
Month(Categorised)   Name     TotalSales  DailySales>100

Nov                  Andrew    350.00      2
                     Brian     160.00      1


Can you suggest some ideas on how to get the calculation in column TotalSales and DailySales>100?
p/s : if daily sales > 100, it'll be counted as one.
Thank you.


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you add a separate column after total sales, where you use a formula like:

@If(@TextToNumber(Field3)>100; 1; 0)

and set totals to that colums (hiding details), I think you've got it!
By the way, I didn't fully understand the Daily sales thing, but the solution may look alike! The key is: you'll need a separate column!
CRAK right there on target.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Pffff... a few minutes later and you would have run off with these points!  ;-))
I wish i could have.  Even now you can donate me.

:-P
Why you wish? I'm sure you already figured out which URL to paste!

Commented:
I'm wondering if cheongkabo is not noticing the Totals option in each column.  Because he is asking how to calculate the sales over 100 (which you provided) but the TotalSales should be automatic if he just goes into the column properties and selects TOTALS:TOTAL under the second tab.
Here comes our favourite Confucious !

;-)
Good point Sno! I even find myself having a hard time finding it every now and then...

Author

Commented:
I understand what snocross said, select Total in second tab under column properties. But that's not what I require here. The view is somehow like a monthly report to display total of sales in a month for each sale person and see how many times he/she achieved the daily sale target which is > 100. If do according to snocross, the total would probably display at the last row of the view. I have thought before to schedule an agent to do the calculation everyday and place the result in a form. But it seems not a correct way to do things. Any better ideas of getting these calculations done?
Commented:
No it wouldn't be in the last column.  Each column has the ability to sum totals.  So in your view you would have 4 columns.

1) Month
Formula: MonthField (Whatever the name is)
Categorized

2) Sales Person
Forumula: SalesPersonField (Whatever the name is)
Categorized

3) Total Sales
Formula: TotalSalesField3
Here in the sorting/categorizing tab you click TOTALS/TOTAL

4) Sales over 100
Formula: @If(TotalSalesField3 > 1; 1;0) This will display a 1 if the record contains a sale over 100 otherwise a 0.
Here in the sorting/categorizing tab you also click TOTALS/TOTAL

Now if you take a look at the view with the name category collapsed it will look just like you want.  If you expand on the categories you will see the details and a 0 or 1 next to each sale.  The point is to keep it collapsed and you will have what you want.

Author

Commented:
Dear snocross
I get what you mean. Subtotal is showed on the same row with the name. Thanks for your help.

Commented:
Glad to help!

-Snocross
Good Work Bro!

Commented:
Thanks bro.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial