Cumulative sum with filter on data column

[jellejansen]

Hi 

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

[GaryGiles]

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.

[jellejansen]

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.

[GaryGiles]

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

[jellejansen]

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)

[GaryGiles]

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)

[jellejansen]

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

[GaryGiles]

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

[jellejansen]

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