topic Re: RangeSum in Script in App Development

RangeSum in Script

americanetsMD — Wed, 06 Dec 2023 14:45:10 GMT

Hello everyone,

consider having the next Table:

As you can see in the image, i have managed to get the Cumulive Sum by using the RangeSum expression.

Anyway, the problem is when i select a specific Period, for example 12.2023, i get 94326, but i need the cumulative amount, 157212. Is there maybe an option to save the cumulative sums in Script, in a separate column?

Important Note: there are multiple clients, which can have same product.

Can anyone give me a hint please.

Re: RangeSum in Script

Or — Wed, 06 Dec 2023 15:01:02 GMT

You can pre-calculate running totals in script - there's plenty of examples on Community - but they will not respond to any filters made within the app, which is typically not desirable behavior. You may find it a better idea to calculate these measures while ignoring selections on the Period field, since you need to start from the earliest available period to get a correct running total.

Re: RangeSum in Script

marcus_sommer — Wed, 06 Dec 2023 15:42:36 GMT

One approach might be to query if a period-selection exists and then branching into different expressions with something like this:

if(len(getfieldselections(Period)), sum({< Period = {"<=$(=max(Period))"}>}, Value),
rangesum(above(sum())))

Of course with some more if-loops you may also react on multiple selected periods and/or other conditions but it could become quite ugly.

Another method would be to wrap the accumulation with an aggr() by ignoring any period-selections within the inner aggregation which would ensure that all cells for the interrecord-functions are there and which makes this calculation unresponsive against the period-selections. Against it you may apply a boolean-check with something like: count(distinct Period) which just returned 0 or 1 and by disabling ZERO results within the properties the excluded periods wouldn't be displayed. But if there are various fields and/or conditions such an approach could be come complex.

Other considerations may go if such a period-selections is really needed - and if for which purpose? And if there is really a sensible reason if it could be done with another (independent) period-field?

Re: RangeSum in Script

vincent_ardiet_ — Wed, 06 Dec 2023 16:24:19 GMT

You can add to your current expression something like this :
+ $(=Sum({<Periode={"<$(=Min(Periode))"}>}Wert*Factor))

Re: RangeSum in Script

americanetsMD — Tue, 12 Dec 2023 16:11:17 GMT

Hi Everyone,
thank you very much for your time and answers... I have managed to get the cumulative sum by using the AsOfTable... But then i get wrong numbers, because of the Faktor field... By using the AsOfTable, i calculate the values * Faktor for each month... However, as you can see in the picture attached, the logic says, calculate Wert and multiply total by current Faktor:
01.2023: 30702,5 * 0,96 = 29474,4

02.2023: (30702,5 + 35940) * 0,97 = 64463,23

...