topic Re: Cumulative sum with Set Analysis in App Development

Cumulative sum with Set Analysis

RighettiAndrea — Sat, 16 Nov 2024 20:26:04 GMT

Hi everyone,
I have an App where are shown some information by month, filtering one year.
In this App there are 2 tables: in the first one there are the values of the year selected, in the second one there are the values of the previous year (using Set Analysis).

One of the fields is a cumulative sum, based on the values shown in the table.
In the first table, I was able to get this value, but in the second one I cannot understand how to get it.

The cumulative sum I'm talking about have this formula:

RangeSum(Before(Sum(([S_ODL.LORDO]-[S_ODL.SCARTITOT])*[DT_ARTICOLI.TCICMEDSTD]/3600),0,NumMese))

(NumMese it's the number that represents the month)

Here the Set Analysis I'm trying to mix with that formula:

{$<Anno={$(=Max(Anno)-1)}>}

(Anno it's my Year field)

I have tried in many ways (like the one below), but nothing to do.

RangeSum(Before({$<Anno={$(=Max(Anno)-1)}>} Sum(([S_ODL.LORDO]-[S_ODL.SCARTITOT])*[DT_ARTICOLI.TCICMEDSTD]/3600),0,NumMese))

Anyone can help me, please?

Thanks a lot.

Re: Cumulative sum with Set Analysis

sunny_talwar — Thu, 18 Jul 2019 18:48:45 GMT

Try to see if one of these work for you

RangeSum(Before(Sum({$<Anno={$(=Max(Anno)-1)}>} [S_ODL.LORDO]-[S_ODL.SCARTITOT])*[DT_ARTICOLI.TCICMEDSTD]/3600), 0, NumMese))

RangeSum(Before(Sum({$<Anno={$(=Max(Anno)-1)}>} [S_ODL.LORDO]-[S_ODL.SCARTITOT])*[DT_ARTICOLI.TCICMEDSTD]/3600), 0, Only({$<Anno={$(=Max(Anno)-1)}>}NumMese)))

Re: Cumulative sum with Set Analysis

RighettiAndrea — Fri, 19 Jul 2019 06:18:35 GMT

Hi,
I've tried but none of these works unfortunately...

With the second one, I get the value of the Month for the previous year but the table now shows also the months of the year selected, filling the cells with all 0 (zero).

(The row I'm talking about, it's the penultimate one).

Thank you.

Re: Cumulative sum with Set Analysis

sunny_talwar — Fri, 19 Jul 2019 11:12:45 GMT

You can remove 0's by unchecking 'Include zero values' under Add-ons -> Data handling

Re: Cumulative sum with Set Analysis

RighettiAndrea — Fri, 19 Jul 2019 12:54:13 GMT

I don't care about zero.

The result should be like the screenschot down here.

The field named "Progressive Sum" is the one I'm trying to create (as I wrote in the first post).

Thankyou!

Re: Cumulative sum with Set Analysis

sunny_talwar — Fri, 19 Jul 2019 12:57:29 GMT

The expression looks okay to me... may be if you are able to share a sample to check this, I might be able to help you better?

Re: Cumulative sum with Set Analysis

RighettiAndrea — Fri, 19 Jul 2019 13:33:22 GMT

The measures in the rectangle are the one I need. I was doing tests with the second one (it's the one I askd for in the post).

Thanks.

Re: Cumulative sum with Set Analysis

sunny_talwar — Fri, 19 Jul 2019 13:42:50 GMT

Try this

RangeSum(Before(Sum({$<Anno={$(=Max(Anno)-1)}>} ([S_ODL.LORDO]-[S_ODL.SCARTITOT])*[DT_ARTICOLI.TCICMEDSTD]/3600), 0, Only({$<Anno={$(=Max(Anno)-1)}>}NumMese)))

Re: Cumulative sum with Set Analysis

RighettiAndrea — Fri, 19 Jul 2019 14:55:05 GMT

It worked perfectly!

Thanks a lot!