Sum of column from slice based on enumlist multipl...

OptimiX_XcrY · 01-22-2022 03:23 AM

Hi there ,

I have a slice based on a Enumlist filter that is set in a one row table. The slice shows the correct data when I select one or more options from the enumlist filter.
I also have a virtual value (in the same one row table) that calculates the sum of [quantity] column from that slice.

The sum VC formula returns a correct value when the slice is based on only one selection from the enumlist filter , but returns 0 when the slice is based on multiple selections from the same filter.

Please , point me in the right directions to fix this sum value. At the moment , it makes no sense to me , since the slice shows the correct data in all instances.

The sum VC is simple , sum(facturianalize[Cant.]) . Same happens when I sum another column , even with “select” included.

Thank you

Suvrutt_Gurjar

Could you please try

SUM(SPLIT(facturianalize[Cant.], “,”))

OptimiX_XcrY

Thanks. it generates an error , The ‘SUM’ function requires a list of numeric inputs . [cant.] is Number

Suvrutt_Gurjar

Thank you. Could you check in a separate VC what
SPLIT(facturianalize[Cant.], “,”) returns? What type of column it returns? Could you also mention what is [Cant.] Column type.

You may also try with
SUM(SPLIT(ANY(facturianalize[Cant.]), “,”))

OptimiX_XcrY

Thanks

It generates a list column , a blank one if slice is based on multiple selection , but a corect one when slice is based on only one filter selection .

Same happens with another test VC , a list , facturianalize[Cant.] .

[cant.] is a number column .

generates the same Sum need numeric inputs error

Suvrutt_Gurjar

Okay thank you.Sorry, not at my desk now. So unable to respond quickly.

Do you mean it is Enumlist with base type number?

Suvrutt_Gurjar

Also if you share relevant screenshots, it will be great.

OptimiX_XcrY

I’ll try.

I have a main readonly table , “facturi” (that means “bills” , containing bills informations , with customers,quantities,price per unit , etc

I have a slice of that table , “facturianalize” (meaning “bills to analyze”).

I have a second table , a one row table , that contains dropdowns (that are used in the slice row filter conditions) , and some VCs to generate some summary informations of the slice (like the SUM VC I choose for this topic).

The dropdowns generates a correct slice , in any combination, with any number of item selected. If I select only one item from any or all dropdowns , the summary VCs, includind the SUM VC , works like a charm , calculationg correctly. If I select two items in any dropdown , everything is 0 , but the slice generated is still correct.

Edit : the dropdowns are created with suggested values formulas , values got FROM the slice , but nothing related to the quantities column

graham_howe

Is there any possibility that there is some problem data in the [cant.] column? As a test, could you change the sum to add up all rows in the underlying table rather than the slice? I know that won’t be filtered, but it will at least rule out any data errors.

OptimiX_XcrY

Thanks.
The test is succesful , it generates the correct sum.
I also eliminated the dot from the [cant.] column , thinking it might cause a bug , but it wasn’t that. And also created a VC in the main table that’s equal to the [cant.] column , and summed that VC column , still generating a 0.

graham_howe

Ok well that was a good test. Next can you try modifying the filter expression on the slice to see if the sum works through it with all row returned?

OptimiX_XcrY

It does. I had nothing to modify , just left the dropdowns blanks , and my slice filter expression returns 100% of the main table

graham_howe

Ok, so with any one filter value selected the sum works, and with no values selected the sum also works. With any combination of values selected the sum returns 0.

I’m not at my computer today, just on a phone, so I can’t test anything, but it does sound like the filter expression on the slice is introducing something strange. Could you paste that expression here?

Update thanks for that, taking a look now.

OptimiX_XcrY

…only if there are multiple selections in a same enumlist dropdown. but the slice updates correct

graham_howe

I must admit that I am stumped here, your code looks fine and as you say the filter is returning the correct rows in the slice, it is just the sum which is not working.

One final test you could try, hard code a couple of customers in the filter in place of the lookup against the enumlist. Again this is only temporary to see if we can force the calculation to work.
Replace this

In([beneficiar];setariAnalize[x6])

With something like

In([beneficiar];List("customer 1","customer 2"))

OptimiX_XcrY

Intriguing test

I must have done something strangely wrong. Both in emulator and in app , the slice result is the unfiltered table. Yet , the VCs in the app shows corect results (calculating from all rows , as slice is equal to the original table) , but in emulator , the VC.s still shows 0.

graham_howe

Yes thanks for the thorough explanation, I had already expected that was your design. I’m just try to see if something in the slice filter could be introducing an issue.

OptimiX_XcrY

my slice filter conditon :

If (and(isnotblank(usersettings(“modul analize”));usersettings(“modul analize”)=“da”);
and(
[DataDoc]>=any(setarianalize[datastart]);
[DataDoc]<=any(setarianalize[datafinal]);
If(isblank(setarianalize[x1]);true;In([NUME PRENUME];setariAnalize[x1]));
If(isblank(setarianalize[x2]);true;In([Departament];setariAnalize[x2]));
If(isblank(setarianalize[x3]);true;In([categorie];setariAnalize[x3]));
If(isblank(setarianalize[x4]);true;In([articol];setariAnalize[x4]));
If(isblank(setarianalize[x5]);true;In([filiala];setariAnalize[x5]));
If(isblank(setarianalize[x6]);true;In([beneficiar];setariAnalize[x6]))
);
False)

OptimiX_XcrY

One dropdown enumlist suggested values :

sort(FacturiAnalize[beneficiar]) (name of costomers)

Steve

How i would write it:

and(
  isnotblank(usersettings(“modul analize”));
  (usersettings(“modul analize”) = “da”);
  isnotblank(
    filter(
      "setarianalize",
      and(
        ([_ThisRow].[DataDoc] >= [datastart]);
        ([_ThisRow].[DataDoc] <= [datafinal]);
        or(isblank([x1]); In([_ThisRow].[NUME PRENUME]; [x1]));
        or(isblank([x2]); In([_ThisRow].[Departament]; [x2]));
        or(isblank([x3]); In([_ThisRow].[categorie]; [x3]));
        or(isblank([x4]); In([_ThisRow].[articol]; [x4]));
        or(isblank([x5]); In([_ThisRow].[filiala]; [x5]));
        or(isblank([x6]); In([_ThisRow].[beneficiar]; [x6]))
      )
    )
  )
)

OptimiX_XcrY

I have a feeling that all of this could somehow be related to the fact that the main table is a read only table , with delta sync enabled, etc

Suvrutt_Gurjar

Thank you for all the details . Could you update what type of columns [X1], [X2],…[X6] are?

OptimiX_XcrY

They are enumlist , text based , with suggested values coming from slice

Suvrutt_Gurjar

Could you once try?

If (and(isnotblank(usersettings(“modul analize”));usersettings(“modul analize”)=“da”);
and(
[DataDoc]>=any(setarianalize[datastart]);
[DataDoc]<=any(setarianalize[datafinal]);
If(isblank(setarianalize[x1]);true;In([NUME PRENUME];SPLIT(setariAnalize[x1],","))),
If(isblank(setarianalize[x2]);true;In([Departament];SPLIT(setariAnalize[x2],","))),
If(isblank(setarianalize[x3]);true;In([categorie];SPLIT(setariAnalize[x3],","))),
If(isblank(setarianalize[x4]);true;In([articol];SPLIT(setariAnalize[x4],","))),
If(isblank(setarianalize[x5]);true;In([filiala];SPLIT(setariAnalize[x5],","))),
If(isblank(setarianalize[x6]);true;In([beneficiar];SPLIT(setariAnalize[x6],",")))
);
False)

OptimiX_XcrY

Thank you. No luck

Suvrutt_Gurjar

[X1]…[X6] columns are in your one row table, that has those other VCs, right?

OptimiX_XcrY

yes

Suvrutt_Gurjar

And the VC that calculates sum you mentioned in the same one row table, is it in the same table, meaning the table haveing columns [X1]…[X6]?

OptimiX_XcrY

yes

WillowMobileSys

I recommend to NOT use Delta Sync. I have had a number of Syncing issues related to that setting being turned on. It just doesn’t perform well enough if you are relying on timely updates.

OptimiX_XcrY

I had some VC issues in some cases , and had to find workarounds. Never managed to identify if it was server caching , delta sync , or the readonly setting of the table. Most of the times I had issues , I have set the table as editable , and used a readonly slice of it.

In this case I disabled server caching , delta sync , and the readonly table , with no luck.
They are useful settings in theory , and I do feel the diference when I have a 80k rows readonly table when opening the app

Steve

A table marked explicitly as READ ONLY will be cached on the server side, so if you’re making changes to the table outside of the app, those changes may not make it to the app even after a sync. To avoid this behavior, use an Are updates allowed? expression of "READ_ONLY".

I don’t use delta sync at all, and recommend against using it. Delta sync will interfere with virtual column updates.

OptimiX_XcrY

I,ll keep using the app with only one options from every enumlists , but I’ll find the time to test some other ideas and come back with an update.

My next step will be to create another one row table , only with VCs , and see if there is a connection , taking into considerations all of the ideas you guys gave me

Suvrutt_Gurjar

May we know in what type of view the one row table , you are setting those enumlist columns and seeing those VCs

OptimiX_XcrY

in a detail view , with quick edit columns , the left one from the picture below

one department selected :

two departments selected :

Suvrutt_Gurjar

Got it. Thank you. I tested on an exactly similar set up. It is working for me with the recommended approaches. I mean using split for enumlist. The below video shows detail view adding two numerical columns based on slice on another table based on the enumlist selections in the detail view. Please take a look at the video below.

I think it will be wiser to troubleshoot further by taking a look at your app. Maybe you wish to engae ApPSheet support or someone else in the community could help.

OptimiX_XcrY

Thank you very much. Will do.

May I ask, why the use of split? It should be fine without it, by using simple sum of column, as described also in the sum formula definition page.

I mean, the slice is properly generated without split too.

Suvrutt_Gurjar

No split is not required for SUM(). Initially I thought your [cantt.] column is enumlist. a SPLIT() is requried for enumlist because a SELECT() on an enumlist creates a list of list and SPLIT() flattens it to a normal list.

So to summarize split is not require for SUM() but I requested SPLIT() to you later again for your filter after knowing your configuration for filter because setariAnalize[x1] and similar expressions create list of list because [X1]… [X6] are enumlists.

In my test also for simple slice expression I use SPLIT() as below because [ProductID] is an enumlist if you see in the video.

Below is slice filter expression

IN([Product ID], SPLIT(SliceDecide[ProductID],","))

OptimiX_XcrY

I understand.

But my slice seems to show and behavie fine with my configuration too (without split used)

Edit :

Suvrutt_Gurjar

Okay. Sorry, I have no explanation for that.

Sum of column from slice based on enumlist multiple selections generates a 0 value