Trend over time calculation based on database values

Ask the community
, ,
1

Hey,

Exam Question: I have a database containing multiple cars that have sold at auction along with their sold price. I want to calculate, for a particular model of car, what the average trend over time % price change of that car’s price is? That time period will vary depending on when is first/most recent sale of that particular model is recorded in the database.

The output of that calculation is to go into the text string by the number 3.

686×579 49.9 KB

Key columns in the database are:
· Car Model (key: ‘model’)
· Price the car sold at (key: ‘priceGbpNumber’, this is stored as a number)
· Data the car sold on (key: ‘auctiondateshort1’, this is stored as a text string in the format “3 Apr 2017”)

My code is below. I assume to do the trend over time, I’d need to use both wixData.aggregate and wixData.query as an extension of what I’ve done for areas 1 and 2 but any help would be greatly appreciated. Have left areas 1 and 2 in the below in case helpful to reference.

// Bring in model name to text string
  wixData.query("Scraper")
  .find()
  .then((results) => {
 let thisName = $w("#dynamicDataset").getCurrentItem()["name"];
 let thisModel = $w("#dynamicDataset").getCurrentItem()["model"];
    $w("#theDetailsString").text = "The details of this " + thisName
 
 // 1 - Similar car count
    wixData.query("Scraper")
      .eq("model", thisModel)
      .count()
      .then( (num) => {
 let numberOfItems = num;
        $w("#similarCarCount").text = numberOfItems.toString() + " " + thisModel + "'s are on TheClassicValuer. \n Check out the details and similar cars below."
        } )
      .catch( (error) => {
 let errorMsg = error.message;
 let code = error.code;
      } )

 // 2 - Average price calculation
 let avgPriceFilter = wixData.filter().eq("model", thisModel);

      wixData.aggregate("Scraper")
        .filter(avgPriceFilter)
        .avg("priceGbpNumber")
        .run()
        .then( (results2) => {
 let items = results2.items;
 let items2 = Math.round(items[0].priceGbpNumberAvg).toLocaleString('en-GB')
          $w("#priceText").text = "The average price for a " + thisModel + " is £" + items2 + ". \n \n That's enough money to buy [enter stat]."
          } )
        .catch( (error) => {
 let errorMsg = error.message;
 let code = error.code;
        })

 // 3 - Trend over time
      $w("#trendText").text = "The " + thisModel + " has [risen/fallen X%] in the past two years. \n \n On that trend, in two years time the price may be [enter price prediction]"
  })