Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
nlee4jnj
New Contributor II

Conditional Running Sum within Table

Hello,

 

Within a table (not a Pivot table) I want a running daily sum  conditional upon a Year column.

If the year for that row is "2019", then I want the sum to be all of the daily values so far where the year is also "2019". If the year for that row is "2018", then I want the sum to be all of the daily values so far where the year is also "2018".

In the picture below, you will see I have the "Nth day of the year" in column 1, Fiscal Year in column 2, and Sales $ in column 3. My current formula is:

RANGESUM(
    ABOVE(
        TOTAL SUM(NetSales)
        ,0
        ,ROWNO(TOTAL)
    )
)

but unfortunately, that gives me a running sum regardless of the year. I want the sum to be conditional on the year column. You can see my desired result on the right in the column below.

table.png

1 Solution

Accepted Solutions

Re: Conditional Running Sum within Table

Try this

Aggr(
RangeSum(Above(Sum(NetSales), 0, RowNo()))
, [Fiscal Year], ([Nth Day of Fiscal Year], (NUMERIC)))
2 Replies

Re: Conditional Running Sum within Table

Try this

Aggr(
RangeSum(Above(Sum(NetSales), 0, RowNo()))
, [Fiscal Year], ([Nth Day of Fiscal Year], (NUMERIC)))
nlee4jnj
New Contributor II

Re: Conditional Running Sum within Table

That was it, Sunny! Thanks for the quick and accurate response.

 

I had tried using AGGR() before, but it didn't work. The key was using (... (NUMERIC)) on [Nth Day of Fiscal Year]. I haven't seen that function before.