Calcolo Media ponderata in Query

di
galantik
il
14 risposte
G
galantik
Pochi posts
Iscritto da
ago, 2014
Messaggi:
38

Calcolo Media ponderata in Query

Salve a tutti.
Chiedo scusa se vi sembrerà una questione banale, ma sono proprio alle prime armi con Access... portate pazienza, pls.
La questione è questa: ho creato una tabella, che contiene dei titoli (con diversa quantità e prezzo). Come faccio a creare una query di selezione che mi calcoli il costo medio ponderato?
Quello che ho fatto è questo:
1. importato i vari campi della tabella (2 volte gli ammontari, di cui una volta non raggruppati e la seconda volta calcolandone una somma)
2. impostato un'epressione del tipo: AveragePrice: ([titoli]![Price]*[titoli]![Amount])/[SumOfAmount]

Il risultato è che non ci sono messaggi di errore, ma non effettua il calcolo che dovrebbe e riporta solo di fatto il campo [Price].
Sbattendoci la testa sopra, credo che il problema sia che non effettua un corretto raggruppamento e che lascia i record separati. Per ovviare a questo problema dovrei togliere (non è neanche sufficiente nascondere) i campi che possono essere diversi (Price e Amount) e lasciare solo il loro raggruppamento Sum. Facendo così però, naturalmente, appare il messaggio di errore "your query does not include the specified expression as part of an agregate function".

scusatemi anche se non mi sono espresso chiaramente....
grazie a tutti

14 Risposte

  • P
    Philcattivocarattere
    Utente Famoso
    Iscritto da
    giu, 2014
    Messaggi:
    2241

    Re: Calcolo Media ponderata in Query

    galantik ha scritto:

    ...Come faccio a creare una query di selezione che mi calcoli il costo medio ponderato?...
    Pubblica la sintassi sql della query (usa il tag code), così ripasso anche come si calcola la media ponderata e sicuramente ne usciamo.
  • G
    galantik
    Pochi posts
    Iscritto da
    ago, 2014
    Messaggi:
    38

    Re: Calcolo Media ponderata in Query

    SELECT  Sum(Equityes.Amount) AS SumOfAmount, Sum(Equityes.Commission) AS SumOfCommission, First(Equityes.ValueDate) AS FirstOfValueDate, Equityes.Amount, Equityes.Price, ([Equityes]![Amount]*[Equityes]![Price])/[SumOfAmount] AS AveragePrice
FROM Equityes
GROUP BY Equityes.Amount, Equityes.Price
HAVING (((First(Equityes.ValueDate))<#8/28/2014#));
    in forma visualizzazione si vede:

    SumOfAmount SumOfCommission FirstOfValueDate Amount Price AveragePrice
    14050 € 170,61 13/08/2014 14.050,00 € 4,00 4
    20600 € 248,75 14/08/2014 20.600,00 € 3,00 3

    L'idea è che faccia (14050*4+20600*3)/(14050+20600) e naturalmente dovrebbe essere lo stesso per tutti i record.
    Il problema mi sembra sia che non fa un corretto raggruppamento: l'unico vincolo che gli ho imposto è che i titoli siano in antecedenti ad una certa data. E questo è sicuramente verificato. Ho provato a nascondere sia Amount che Price, ma il risultato non cambia. Cambia solo se cancello drasticamente i campi Amount e Price ma a quel punto devo cancellare anche AveragePrice, che ha bisogno di questi singoli campi.

    Scusami se non mi spiego bene...
  • @Alex
    @Alex
    Super Famoso
    Iscritto da
    feb, 2012
    Messaggi:
    8751

    Re: Calcolo Media ponderata in Query

    Sfruttando i LOGARITMI...
    Questo ricava una Media Geometrica Ponderata: 
    SELECT Exp(Sum(Log(CampoValori)*Frequenza)/Sum(Frequenza)) AS MediaGp FROM T1
    Per Frequenza chiaramente è il Numero dei Campioni...
  • G
    galantik
    Pochi posts
    Iscritto da
    ago, 2014
    Messaggi:
    38

    Re: Calcolo Media ponderata in Query

    Alex grazie per il tuo tempo e scusami se non mi riesco a spiegare bene...
    non credo sia un problema di formula (logaritmo piuttosto che somme e divisioni). Il problema mi sembra sia che non faccia il raggruppamento che dovrebbe fare.

    Se, riprendendo il tuo esempio, "Frequenza" cambiasse di record in record (come nel mio caso cambia il campo "Prezzo") non farebbe nessun raggruppamento e quindi sum(Frequenza) diventerebbe uguale a Frequenza di ciascun record. Ergo MediaGp sarebbe uguale a Exp(Sum(Log(CampoValori)) per ciascun record.
    Questo è il risultato della mia query:

    SumOfAmount||SumOfCommission|| FirstOfValueDate|| Amount|| Price|| AveragePrice
    14050 || € 170,61|| 13/08/2014|| 14.050,00||€ 4,00|| 4
    20600 || € 248,75 || 14/08/2014|| 20.600,00 ||€ 3,00|| 3

    il fatto che non faccia il raggruppamento lo vedi dalle colonne sumofamount (dove dovrebbe esserci 34650), sumofcommission (eur 419,36) e FirstOfValueDate (dovrebbe riportare 13/08/2014).
    Scusami di nuovo. Sto cercando di spiegarmi meglio che posso e per voi saranno senz'altro domande banali e scontate...
  • P
    Philcattivocarattere
    Utente Famoso
    Iscritto da
    giu, 2014
    Messaggi:
    2241

    Re: Calcolo Media ponderata in Query

    galantik ha scritto:

    ...
    SumOfAmount||SumOfCommission|| FirstOfValueDate|| Amount|| Price|| AveragePrice
    14050 || € 170,61|| 13/08/2014|| 14.050,00||€ 4,00|| 4
    20600 || € 248,75 || 14/08/2014|| 20.600,00 ||€ 3,00|| 3
    mi sto perdendo... il calcolo "ponderato" dovrebbe essere fatto in base al prezzo che cambia di giorno in giorno? Che relazione esiste tra amount e prezzo?
  • G
    galantik
    Pochi posts
    Iscritto da
    ago, 2014
    Messaggi:
    38

    Re: Calcolo Media ponderata in Query

    Il prezzo ponderato rimane lo stesso fino ad una nuova operazione. Ma il problema è che non sta raggruppando come dovrebbe.
    Il risultato corretto dovrebbe essere:
    SumOfAmount||SumOfCommission|| FirstOfValueDate|| AveragePrice
    34650||429,36||13/08/2014||34.650,00||3.40548

    per far questo dovrei naturalmente cancellare le colonne Amount e Price che, essendo diverse, non mi consentirebbero il raggruppamento. ma se le cancello non riesco più a fare AveragePrice che è ottenuto come: (14050*4+20600*3)/(14050+20600)
    amount e price non sono legate tra di loro direttmanete. sono presi come campi staccati da una tabella. Certo che se ci fosse un'altra operazione cambierebbe il risultato. ad esempio con

    Amount Price Commission ValueDate
    20.600,00 € 3,00 € 248,75 14/08/2014
    14.050,00 € 4,00 € 170,61 13/08/2014
    1.000,00 € 5,00 € 170,61 28/08/2014

    il risultato riportato diventa:
    SumOfAmount SumOfCommission FirstOfValueDate Amount Price AveragePrice
    1000 € 170,61 25/08/2014 1.000,00 € 5,00 5
    14050 € 170,61 13/08/2014 14.050,00 € 4,00 4
    20600 € 248,75 14/08/2014 20.600,00 € 3,00 3

    mentre vorrei:
    SumOfAmount SumOfCommission FirstOfValueDate AveragePrice
    44650 589.97 13/08/2014 3.4502

    con averageprice che adesso è calcolato come (1000*5+14050*4+20600*3)/(1000+14050+20600)
  • @Alex
    @Alex
    Super Famoso
    Iscritto da
    feb, 2012
    Messaggi:
    8751

    Re: Calcolo Media ponderata in Query

    Il Raggruppamento non lo puoi fare se hai tutti quei CAMPI SPLITTATI...!
    Hai provato la soluzione che ti ho prospettato...?

    Esempio
    
Tabella: T1
Evento Frequenza
14        2
13        3
11        5
    Ora per fati l'Esempio usa la Foruma che ti ho dato prima:
    
SELECT Exp(Sum(Log(Evento)*Frequenza)/Sum(Frequenza)) AS MediaGp FROM T1
    Ottengo:12,136821

    Se a tuo avviso il risultato va bene, prova ad usare il tuo metodo, altrimenti usa questo sul tuo caso.
  • P
    Philcattivocarattere
    Utente Famoso
    Iscritto da
    giu, 2014
    Messaggi:
    2241

    Re: Calcolo Media ponderata in Query

    galantik ha scritto:

    ...
    Amount Price Commission ValueDate
    20.600,00 € 3,00 € 248,75 14/08/2014
    14.050,00 € 4,00 € 170,61 13/08/2014
    1.000,00 € 5,00 € 170,61 28/08/2014
    ...
    mentre vorrei:
    SumOfAmount SumOfCommission FirstOfValueDate AveragePrice
    44650 589.97 13/08/2014 3.4502

    con averageprice che adesso è calcolato come (1000*5+14050*4+20600*3)/(1000+14050+20600)
    Mi sembra fin troppo facile 
    SELECT Sum(equityes.Amout) AS SumOfAmount, Sum(equityes.Commission) AS SumOfCommission, First(equityes.Valuedate) AS FirstOfValueDate, Sum(Equityes.Amout*Equityes.Price)/Sum(equityes.Amout) AS AveragePrice
FROM equityes
WHERE equityes.valuedate<#8/28/2014#;
    Mi sfugge qualcosa?
  • G
    galantik
    Pochi posts
    Iscritto da
    ago, 2014
    Messaggi:
    38

    Re: Calcolo Media ponderata in Query

    Non va...
    Creata la tabella:

    evento|| frequenza
    14|| 2
    13|| 3
    11|| 5

    e creata la seguente query: 
    SELECT prova.evento, prova.frequenza, Exp(Sum(Log([Evento])*[Frequenza])/Sum([Frequenza])) AS Expr1
FROM prova
GROUP BY prova.evento, prova.frequenza;
    il risultato è:

    evento|| frequenza|| Expr1
    11|| 5|| 11
    13|| 3|| 13
    14|| 2|| 14

    Diciamo che non io non sia interessato a visualizzare le colonne evento e frequenza. mi aspetterei che defleggando il campo "show" ci fosse un raggruppamento per tutto e che la media che tu mi hai dato fosse calcolata e presentata su un'unica riga. invece rimane:
    Expr1
    11
    13
    14
  • P
    Philcattivocarattere
    Utente Famoso
    Iscritto da
    giu, 2014
    Messaggi:
    2241

    Re: Calcolo Media ponderata in Query

    @Alex ha scritto:

    Sfruttando i LOGARITMI...
    Questo ricava una Media Geometrica Ponderata: 
    SELECT Exp(Sum(Log(CampoValori)*Frequenza)/Sum(Frequenza)) AS MediaGp FROM T1
    Per Frequenza chiaramente è il Numero dei Campioni...
    Ho imparato che esiste una media ponderata e una media geometrica ponderata
  • G
    galantik
    Pochi posts
    Iscritto da
    ago, 2014
    Messaggi:
    38

    Re: Calcolo Media ponderata in Query

    Philcattivocarattere ha scritto:

    galantik ha scritto:

    ...
    Mi sfugge qualcosa?
    Phil non so cosa dire.
    capisco il tuo codice ma da me non funziona... comincia col chiedermi Enter Parameter Value per equity.Amount
    se gli butto dentro un valore arbitrario di 1000 mi esce:

    SumOfAmount SumOfCommission FirstOfValueDate AveragePrice
    3000 € 589,97 14/08/2014 4

    che dipenda da qualche impostazione di Access, che da me è in inglese?
  • @Alex
    @Alex
    Super Famoso
    Iscritto da
    feb, 2012
    Messaggi:
    8751

    Re: Calcolo Media ponderata in Query

    galantik ha scritto:

    Non va........
    Io quando sento dire NON VA... inizio ad innervosirmi... soprattutto se non ci sono le condizioni per capirsi.

    Innanzitutto hai modificato la Query SQL senza alcuna cognizione di causa, potrei dirti che non hai chiaro come funziona il Group BY, ma non lo faccio...

    Limitati a fare il COPIA/INCOLLA di quello che ti ho detto... poi se non va ne parliamo in modo TECNICO non come si sta facendo ora...
  • P
    Philcattivocarattere
    Utente Famoso
    Iscritto da
    giu, 2014
    Messaggi:
    2241

    Re: Calcolo Media ponderata in Query

    galantik ha scritto:

    Phil non so cosa dire.
    capisco il tuo codice ma da me non funziona... comincia col chiedermi Enter Parameter Value per equity.Amount
    se gli butto dentro un valore arbitrario di 1000 mi esce:

    SumOfAmount SumOfCommission FirstOfValueDate AveragePrice
    3000 € 589,97 14/08/2014 4

    che dipenda da qualche impostazione di Access, che da me è in inglese?
    Allora... se hai copiaincollato... c'è un errore (o più) di sintassi, perché ho cercato di ricreare la tua struttura delle tabelle ma forse qualche campo non l'ho digitato ben bene. Ad esempio nelle mio codice ho scritto [Amout] invece è sicuramente [Amount] poi occhio alla data, come è formattata (giorno mese anno o la sequenza giusta per te) e il segno di < o > che, sempre nelle mie prove, avevo modificato. Controlla in generale quindi il nome dei campi (ho scritto [Amout] in due posizioni)
  • G
    galantik
    Pochi posts
    Iscritto da
    ago, 2014
    Messaggi:
    38

    Re: Calcolo Media ponderata in Query

    OK.
    Grazie mille Phil. Adesso funziona e, cosa più importante, ho capito perché non funzionava e dove sbagliavo.
    In sostanza usavo la costruzione guidata dell'espressione per il calcolo, che mi portava a scrivere:
    AveragePrice: ([Equityes]![Amount]*[Equityes]![Price])/[SumOfAmount]

    Ma per farla funzionare dovevo inserire i campi Price e Amount, che mettevo in modalità Group By.
    E qui stava l'errore: anche se non visualizzati rimanevano campi comunque diversi e quindi non portava al raggruppamento che volevo.
    Invece scrivendo proprio:
    AveragePrice:sum(equityes.amount*equityes.price)/sum(equityes.amount)
    tutto funziona, perché non devo inserire campi inutili nella query.

    URGE UNA LETTURA APPROFONDITA DI UN MANUALE DI SQL!!!!!!

    comunque grazie mille per l'aiuto: è stata una bella lezione.

    p.s. Alex non ti innervosire, ti prego. Ho capito e fatto girare la tua query, che funziona naturalmente bene. Ho capito dove sbagliavo. E del resto era ovvio che fossi io a sbagliare ma, come anticipato, sono proprio all'inizio!
Devi accedere o registrarti per scrivere nel forum
14 risposte