Aggr(Rangesum()) doesn't count off-table data

[Gestion-PSD]

Hi all!

We're facing issues with an Aggr(RangeSum(...)) expression.

I'd try to explain the situation:

We want a straigh table with WeekName as dimension and a cummulative Count as measure. (It should be a straigh table for Nprinting reports).

Our WeekName dimension should be 'truncated' to show from week 2019/21 to now, but there're older data that must be accumulate in the first row (WeekName 2019/21 should show its count plus previous weeks count)

We'd tryed with

Aggr(RangeSum(Above(Count({SetAnalysis} distinct[ID]), 0, RowNo())),WeekName)

as measure. It works almost fine, but not at all. This expression in fact show the accumulate of older weeks in the first row, but it shows nulls in rows with no data. (see image below, column "1.PA")

We'd also tryed with the measure

RangeSum(Above(Count({SetAnalysis} distinct[ID]), 0, RowNo()))

and also works almost fine. This expression show te accumulate of above rows in each row, bot it doesn't show data older tan first week (see image below, column "1. PA")

We're loonkg for an expression that "mix" both behaviors: Show the accumulate of older weeks in first row, and the accumulate of above rows in each row. I mean, Column 1.PA should show values 2,2,2,5,5,13,20,20,24,...

If you have a look to "Total" column, it show the desired data with

Aggr(RangeSum(Above(Count({SetAnalysis} distinct[ID]), 0, RowNo())),WeekName)

The only difference between both expression is the SetAnalysis: Expression for "1. PA" column includes {<[TaskType] = {'1. PA'}>} and expression for "Total" column doesn't.

Can someone help us?

Thanks in advance.

[sunny_talwar · Accepted solution]

Try this

Aggr(
  RangeSum(Above(
    Count({SetAnalysis} DISTINCT [ID]) + Sum({1} 0)
  , 0, RowNo()))
, WeekName)

[Gestion-PSD]

Thanks for that quick reply. It seems to work fine.