"Custom aggregate formula" , or virtual column for slice only


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 :

  1. 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.

  2. 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 ?

Thank you

1 Like

May I ask do you think using Usersettings for the filter would be too “not dynamic” solution to your case?

I am the only user, but I’ll check usersettings, I am not familliar with those options yet.

No, it doesn’t fits this needs.

Please check the sample app called “Filtered Dashboard”. You can find that app from this page… https://www.appsheet.com/portfolio/531778

1 Like

I studied it before I opened this thread. But I couldn’t split my large table.

There is no such thing for now as a “custom” aggregate formula , to overwrite the default ones , or a way to make VC only run for slice rows ?

May I ask what do you mean by this “split my large table”?

It helps if I can avoid creating additional tables.

I need a VC that calculates an average price for a small slice of a large table , without applying to the largr table too (for performance reasons).

Right now I am trying a closer step, by using an additional table with VCs to get values based on slice values, but for some reason my sum of select expression returns nothing but zero.


FacturiAnalize is a slice , containing columns like “total” and “articol” . Slice is not empy

A second table contains only one column , with unique “articol” values , and a VC for the sum/select formula.

Is there anything wrong here? Same type formula works in a sumarry table , for a single “articol” .

Sum(FacturiAnalize[total]) also returns 0

Correct, there is no way to provide custom aggregate expressions.

Correct, there is no way to attach a VC to a slice only.

Have you tried using the Test feature from Expression Assistant?

Yes. Still 0 , but no clue why

Does you SELECT() expression return anything?

No . Even Sum(FacturiAnalize[total]) returns 0

What does the expression, FacturiAnalize[total] (without SUM()) produce in Test?

In test it shows me the current table , but nothing in VC column (blanks)

Please post a screenshot of the Test output of the expression, FacturiAnalize[total].

I posted screenshots editing previous message, because I was limited to the number of messeges.

In the meantime, i kept trying. I discovered something strage.

I was using 4 conditions for my slice, last two of them being dropdown type, using the formula :

contains([NUME PRENUME];any(setarianalize[x1]));

This was very useful because I could show a view of slice for a name and a product, or for all names and products (by “cleaning” the drpdowns to blanks).

But, in VCs, as described in my previous messeges, the slice was considered “empty” , altough it was shown perfect in a view.

Any idea why? Thank you