Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
RighettiAndrea
New Contributor

Cumulative sum with Set Analysis

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.

1 Solution

Accepted Solutions

Re: Cumulative sum with Set Analysis

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)))
8 Replies

Re: Cumulative sum with Set Analysis

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))

or

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

 

RighettiAndrea
New Contributor

Re: Cumulative sum with Set Analysis

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

Annotazione 2019-07-19 081508.png

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

Thank you.

Re: Cumulative sum with Set Analysis

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

RighettiAndrea
New Contributor

Re: Cumulative sum with Set Analysis

I don't care about zero.

The result should be like the screenschot down here.

Annotazione 2019-07-19 145140.png

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

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?

RighettiAndrea
New Contributor

Re: Cumulative sum with Set Analysis

Annotazione 2019-07-19 152705.png

 

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

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)))
RighettiAndrea
New Contributor

Re: Cumulative sum with Set Analysis

It worked perfectly!

Thanks a lot!