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

AppSheet AppSheet Q&A
,
1

Hello,

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.

3X_0_c_0cbcf33aa2e8b8ffa990d533cee8ac7ea11eec07.png
3X_0_c_0cbcf33aa2e8b8ffa990d533cee8ac7ea11eec07.png1440×927 15 KB

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
2

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

3

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

4

No, it doesn’t fits this needs.

5

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

2 Likes
6

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 ?

7

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

8

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.

sum(select(FacturiAnalize[total];([articol]=[_thisrow].[articol])))

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

9

Sum(FacturiAnalize[total]) also returns 0

10

OptimiX_XcrY:

There is no such thing for now as a “custom” aggregate formula , to overwrite the default ones

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

OptimiX_XcrY:

or a way to make VC only run for slice rows

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

11

OptimiX_XcrY:

Is there anything wrong here?

Have you tried using the Test feature from Expression Assistant?

12

Yes. Still 0 , but no clue why

13

Does you SELECT() expression return anything?

14

OptimiX_XcrY:

Sum(FacturiAnalize[total]) also returns 0

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

15

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

16

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

3X_a_f_af0df71683ad40d71eb61030a2e3d98ff7305574.png
3X_a_f_af0df71683ad40d71eb61030a2e3d98ff7305574.png1920×1080 206 KB

3X_4_c_4cac06ce9670c99aad00fef8e30a728b0a0b537c.png
3X_4_c_4cac06ce9670c99aad00fef8e30a728b0a0b537c.png1920×1080 284 KB

17

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

18

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 :

and(
[DataDoc]>=any(setarianalize[datastart]);
[DataDoc]<=any(setarianalize[datafinal]);
contains([NUME PRENUME];any(setarianalize[x1]));
contains([articol];any(setarianalize[x2]))
)

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