Please excuse my poor english. I am gonna try to describe my situation as simple as possible.
What I have :
A table with over 60000 lines of sales, containing about 15 columns, most important being : date, product, quantity, unit price.
What I need :
A “real time” view listing all products and their average prices , between two dates. (A custom group aggregate formula could do this).
What I sucsesfully did so far :
A view of a summary table , that contains quick edit columns for “StardDate” , “EndDate” and “Product” , and showing virtual colums based on Select formulas that are showing the TotalQuantity, the TotalValue, and TotalValue/TotalQuantity for the selected product. This works fine, but I can only see info for only a product at once. A screenshot is attached bellow.
What I “studied” , with no success :
The idea of virtual columns in the main table would destroy the performance , creating more then 600000x60000 calculations for every virtual column. I rejected this idea.
If there was a posibility to only run virtual columns for the slice, it would be perfect, but I think VCs are computed on loading ,for the main table, before the slice is created. I don’t know a way to make a VC to only check and comute values from the slice only.
At the start of my app project , I made everything work in Gsheets , with formulas in Gsheets. But it was no “real time” view, and after I selected the start and end dates, I had to wait for about 5 to 10 minutes (because I have that setting that refreshes the rows only after it detects a change…Delta I think), then refresh, then I could see the desired results.
Do you have any ideas for achieving what I need ?