2. PHPRunner General questions
  3. Calculated fields
This topic is locked

Calculated fields

 		6/24/2006 13:04:53
PHPRunner General questions
O
 osluk author

Chateau Appellation 2005 en 2004 en 2000 en 2005 Estimate GBP Maturity GQ RP JR

(Alpha format) primeur GBP primeur GBP primeur GBP
Mouton Rothschild Pauillac 3500.00 900.00 1,800.00 2500 - 3000 2013 - 2030 95 - 97 94 - 96 18.5

Latour Pauillac 4000.00 900.00 1,800.00 2500 - 3500 2025 - 2045 96 - 100 98 - 100 19.5

Valandraud St-Emilion 1780.00 850.00 1,280.00 800 - 1200 2013 - 2025 93 - 95 93 - 96 18.5

Lafite Rothschild Pauillac 3700.00 900.00 1,800.00 2500 - 3500 2015 - 2035 98 - 100 93 - 96 19.5

Margaux Margaux 4000.00 900.00 1,800.00 2500 - 3500 2015 - 2035 98 - 100 96 - 100 19.5


I have the current data and fields for "2005 over 2004 - increase ex chateau" & "2005 over 2000 - increase ex chateau" is there a way to calculate this information and populate the appropriate fields?
Cheers Chris
G
 		giles 6/24/2006

Hi Chris,

If what you are trying to do is...
G
 		giles 6/24/2006

Hi Chris,

I'll try that again...
If what you are trying to do is...

  1. Sum the sales of chateau for 2004
  2. Sum the sales of chateau for 2005
  3. Calculate (sales for 2005)-(sales for 2004) / (sales for 2004) * 100
    then the best way may be to do it in your mysql query.

    I have used...
    SUM(IF (`fieldname1`=somevalue,1,0)) AS `totaloffieldname1`
    to total up the number of entries where `fieldname1` has been set equal to somevalue.
    Now I'm assuming you have a table for sales in which their are fields that record year of purchase and qty purchased...If so then you may be able to use:

    (SUM(IF(`year`="2005",qty,0)-SUM(IF(`year`="2004",qty,0))/SUM(IF(`year`="2004",qty,0) as `myanswer`
    Got the idea from this mysql item...http://dev.mysql.com/doc/refman/5.0/en/select.html

    Scan down until you see the the first user comment...
    Hope this helps.
    Giles.
O
 		osluk author 6/25/2006

Thanks Giles,
Perhaps I should be a bit clearer in my explaation.
But getting the data entered first then running a MySql query to populate the required values does seem to make the most sense.
The site is to aid users choose and buy wine.
So "2005 over 2004 - increase ex chateau"

= 2005 price - 2004 price expressed as a percentage of 2004 price
& "2005 over 2000 - increase ex chateau"

= 2005 price - 2000 price expressed as a percentage of 2000 price
2005 price UK increase 2005 over 2004 increase 2005 over 2000

Margaux Margaux £4,000 338% 192%

Latour Pauillac £4,000 289% 192%

Lafite Rothschild Pauillac £3,700 275% 150%
Margaux 2005 Margaux 4000.00 900.00 1,800.00

Lafite Rothschild 2005 Pauillac 3700.00 900.00 1,800.00

Latour 2005 Pauillac 4000.00 900.00 1,800.00
Alexey admin 6/26/2006

Chris,
here is the code for the calculated fields you can use in your SQL query:

select

...

(price2005/price2004 - 1)100 as increasefrom2004,

(price2005/price2000 - 1)100 as increasefrom2000,

...
O
 		osluk author 6/28/2006

(('Release Price GBP'/'2004 primeur price GBP') - 1)100 as '2005 over 2004 - increase ex chateau',

(('Release Price GBP'/'2000 primeur price GBP') - 1)100 as '2005 over 2000 - increase ex chateau',
The figures are as follows
Chateau - Vintage - 2005 en primeur GBP - 2004 en primeur GBP - 2000 en primeur GBP

Mouton Rothschild - 2005 - 3500.00 - 900.00 - 1,800.00

Chateau Pichon-Longueville Baron - 2005 - 740 - 340 - 360
3500/900 = 3.88888 - 1 = 2.8888

3500/1800 = 1.944444444 -1 = .944444
740/340 = 2.176470588 -1 = 1.176470588

740/360 = 2.055555556 -1 = 1.055555556
Even with values in the table each value shows as -100%


('Release Price GBP'/'2004 primeur price GBP' - 1) as '2005 over 2004 - increase ex chateau',

('Release Price GBP'/'2000 primeur price GBP' - 1) as '2005 over 2000 - increase ex chateau',



`2005 over 2004 - increase ex chateau`,

`2005 over 2000 - increase ex chateau`,

This is perfect but only if manually calculated in advance!


('Release Price GBP'/'2004 primeur price GBP' - 1) as `2005 over 2004 - increase ex chateau`,

`2005 over 2000 - increase ex chateau`,
Another failed attempt from by bubling experiments.

('Release Price GBP'/'2004 primeur price GBP' - 1) as `2005 over 2004 - increase ex chateau`,
I have noticed the ` and the ' seem to be critical!
Cheers Chris
Alexey admin 6/28/2006

Use tickles (`) around the field names.

Here is the correct expression:
(`Release Price GBP`/`2004 primeur price GBP` - 1) as `2005 over 2004 - increase ex chateau`,
O
 		osluk author 6/28/2006

Erekaa
Thanks for that, that is perfect.