Need help with set analysis in a table

agrawalpavan — Mon, 06 Aug 2018 19:30:13 GMT

Hi all,

thanks in advance for your help.

There is a table with Date, month and year and an amount field called "RevAmount" and a master calendar associated with it. There is one dimension field called "Salesperson".

Created a "Table" chart with these fields.

I need to refer to sum(RevAmount) of previous month on this table.

For example if I have filter pane selected to year 2018 and the table is showing SUM(RevAmount) by Month, I have 12 rows one for each month (for year 2018). Now On each row I need to refer to SUM(RevAmount) for previous month. For 2018 January I need to get the value for 2017 December which is not part of the selection (Year=2018).

I tried using _MonthSerial and that is not working.

This formula is not working way I want.

SUM({1<Year=,Month=,[_MonthSerial]=,[_MonthSerial] = {"=$(=Max([_MonthSerial])-1)"} >} RevAmount)

Re: Need help with set analysis in a table

sunny_talwar — Mon, 06 Aug 2018 20:25:29 GMT

Try this instead

Aggr(Above(Sum({<Year, Month, [_MonthSerial], [_MonthSerial]>} RevAmount)), MonthYear)

Re: Need help with set analysis in a table

agrawalpavan — Mon, 06 Aug 2018 20:32:37 GMT

Sunny,

This is awesome. It works.

Really appreciate it.

Thank you,

Pavan

topic Re: Need help with set analysis in a table in App Development

Need help with set analysis in a table

Re: Need help with set analysis in a table

Re: Need help with set analysis in a table