topic Re: Cumulative sum with filter on data column in App Development

Cumulative sum with filter on data column

jellejansen — Wed, 22 Dec 2021 20:40:09 GMT

I have a long format table, attached as xls doc. The table contains three columns, date, type (which currency) and the daily return. I want to create a chart that shows the cumulative return only for the EUR (so not the USD). I do not want to use the data load editor for this. The chart should look as follows:

I have tried setting the 'modifier' to 'Accumulated' but then it shows all currencies and not just the EUR.

Any ideas on doing this?

Thanks,

Jelle

Re: Cumulative sum with filter on data column

GaryGiles — Fri, 09 Apr 2021 10:26:09 GMT

Have you tried adding add set analysis to your expression?

sum({$<Type={'EUR'}>} Return)

Then, use Accumulated.

If you only have 1 value per Date for EUR, sum should work. You could also use Avg or Only.

Re: Cumulative sum with filter on data column

jellejansen — Fri, 09 Apr 2021 10:31:03 GMT

Yes I have tried that but it doesn't work because i have multiple returns per date (which I should have shown in the xls example I suppose). So every date will have multiple returns because i have several different currencies.

Re: Cumulative sum with filter on data column

GaryGiles — Fri, 09 Apr 2021 10:45:11 GMT

Does each currency on have 1 entry per date? Are there mulitple entries per Date for Type='EUR'?

Re: Cumulative sum with filter on data column

jellejansen — Fri, 09 Apr 2021 10:49:11 GMT

I have updated the xls so the example is a bit more clear. each currency has one return figure per date and the dates are the same for all currencies (as seen in the updated data example)

Re: Cumulative sum with filter on data column

GaryGiles — Fri, 09 Apr 2021 10:57:56 GMT

Based on the data in the spreadsheet, this statement should work fine. Note: I did change the capitalization to match the field names in the spreadsheet.

sum({$<TYPE={'EUR'}>} RETURN)

Re: Cumulative sum with filter on data column

jellejansen — Fri, 09 Apr 2021 11:12:09 GMT

Well that does not give the cumulative return, just the daily return. I suspect we need to do something with Aggr, RangeSum and Above

Re: Cumulative sum with filter on data column

GaryGiles — Fri, 09 Apr 2021 11:42:58 GMT

You should be able to set the Modifier to Accumulation for the measure in your line chart.

Re: Cumulative sum with filter on data column

jellejansen — Fri, 09 Apr 2021 13:10:58 GMT

Yes but like I mentioned in the question this does not work because then the filter on TYPE is lost and all currencies are shown in the chart