Cumulative measure in line chart

[Amit_B]

Hi,

I posted the same issue a week ago, a colleague tried to help me but we're still stuck.

We managed to built a line chart with one dimension (MonthID) and 5 measures (one for each year). The measures are identical except of set analysis which defined the year to refer to. Each measure has its own title and 'show condition' specific to the relevant year.
The line chart displays the correct values according to the display logic the client asked for - data is display up tp the selected MonthID or the max MonthID.

This is the first measure:
{<Year={"$(vCumu_Year1)"}>}
{<Quarter=, MonthID=, Date={"<=$(vMaxDate)"}>}
If(MonthID > vMaxMonth or Sum(Items) = 0,
    Null(),
    RangeSum(Below(Sum(Items), 0, NoOfRows())) / RangeSum(Below(Count(distinct Date), 0, NoOfRows()))
)

Then we tried to remove the set analysis that refers to the year, and replace it with adding Year as dimension.
That way, in theory, we could use just one measure instead of five.
But now the values are wrong, and whenever we try to fix something' something else breaks.

We'd be grateful for any assistance.

[rubenmarin]

Hi, have you tried a simpler expression? like:

{<MonthID={"<=$(=Max(MonthID))"}>}(Sum(Items) / Count(Distinct Date))

And check the option to full accumulate values

[Amit_B]

Hi,

We tried but it didn't fully work.
The display condition work fine, but the values displayed are incorrect.

For example:
The user selected years 2024 & 2025, and month 2. So, as it should be, there is a separate line for each year selected.
But the data is summed up by month, not by year.

Look at the attached screenshot.
I'm using below(), so the data for 2025 is not cumulative (it simply show the value for each month), and the data for 2024 is cumulative by month and includes the data from 2025.
AvgJan24 = (ItemsJan25+ItemsJan24) / (DaysJan25+DaysJan24)

[rubenmarin]

Hi, I'm not sure of what you want... jan'25 has to sum jan'23, jan'24 and jan'25? and feb'25 has to sum feb'23, feb'024 and feb'25 (but not any of the jan values)?

And Jan'24 has to sum jan'23 and jan'24?

[Amit_B]

Sorry if I wasn't clear.

This is the desired results when the user selects the years 2024 & 2025 and month 2:

Line for year 2024:
AvgJan24 = ItemsJan24 / DaysJan24 = 32,545
AvgFeb24 = (ItemsJan24 + ItemsFeb24) / (DaysJan24 + DaysFeb24) = 32,435

Line for year 2025:
AvgJan25 = ItemsJan25 / DaysJan25 = 33,877
AvgFeb25 = (ItemsJan25 + ItemsFeb25) / (DaysJan25 + DaysFeb25) = 33,773

[rubenmarin · Accepted solution]

Hi, one somewhat easy way to get the result in this case is to create the line chart with only one part of the operation, in example just Sum(Items), set the modifidier to full accumulate, and copy the result of the 'output expression' box. And do the same with the divisor.

The expresion you need could be: ([OutputExpression1])/([OutputExpression2])

At the end, replacing the GetObjectField to field names used:

// Accumulation of Sum(Items)
(Sum({1<MonthID={">=$(=Min(MonthID))<=$(=Max(MonthID))"},Year={">=$(=Min(Year))<=$(=Max(Year))"}>}
  Aggr(RangeSum(Above(If(Count(MonthID) * Count(Year) > 0,  (Sum(Items))
    + Sum({1<MonthID={">=$(=Min(MonthID))<=$(=Max(MonthID))"},Year={">=$(=Min(Year))<=$(=Max(Year))"}>}0), 0), 0, RowNo()))
  ,(Year, (Numeric, Ascending), (Text, Ascending)), (MonthID, (Numeric, Ascending), (Text, Ascending)))))

// Divide by accumulation of Sum(NumOfDates) (or use the needed expression)
/(Sum({1<MonthID={">=$(=Min(MonthID))<=$(=Max(MonthID))"},Year={">=$(=Min(Year))<=$(=Max(Year))"}>}
  Aggr(RangeSum(Above(If(Count(MonthID) * Count(Year) > 0,  (Sum(NumOfDates))
    + Sum({1<MonthID={">=$(=Min(MonthID))<=$(=Max(MonthID))"},Year={">=$(=Min(Year))<=$(=Max(Year))"}>}0), 0), 0, RowNo()))
  ,(Year, (Numeric, Ascending), (Text, Ascending)), (MonthID, (Numeric, Ascending), (Text, Ascending)))))

[Amit_B]

Thank you so much for your help!
It works perfectly now.