Set Analysis with cumulative data and range with max date

Polor1993 — Wed, 23 Nov 2022 14:46:33 GMT

Previously I needed help to create a formula to a line chart. I created a chart using set analysis to specify a range of the axis x (I want just a sample of the dimension I selected), and at the same time I want it to be a cumulative line graph. For example:

Month Value
November 2020 300
December 2020 150
January 2021 250
February 2021 175
March 2021 450

In this example, I want my graph to cover only between December 2020 and March 2021 and to be cumulative, with 150 for December, 400 for January, 575 for February. So I came up with an expression like that:

=RangeSum(Above(Sum({<Month={"*"}-{'November 2020'}>}Value),0,RowNo()))

But how can I apply the same idea but always to the range between the maximum date (month and year) and the 12 months previous that maximum date? I tried to use the max function inside the set analysis, but it doesn't work. Please, I need some help.

Thanks a lot!

Re: Set Analysis with cumulative data and range with max date

williejacobs — Thu, 01 Dec 2022 09:07:11 GMT

Hi @Polor1993

I would create a variable for this purpose, in script for me usually works best.
For the period/month for your formula, you can create a variable with 'AddMonths(Max(Date),-12)'.
You can use this variable in your formula.
=RangeSum(Above(Sum({<Month={">=$(vDateVariable)"}>}Value),0,RowNo()))

topic Set Analysis with cumulative data and range with max date in App Development

Set Analysis with cumulative data and range with max date

Re: Set Analysis with cumulative data and range with max date