Set Analysis: Need To Look At Data From Previous Month

[bdroesch]

Hi All,

I have month end balances for different accounts. I am trying to create a table where a user can select multiple account numbers and multiple dates to view the current balance as well as the balance from the previous month end.

Below are examples of the results I would be trying to produce where Account, AsOfDate & CurrBalance are the only fields that are being loaded in via the data loader. (So PrevMonthEndDate & PrevBalance would need to be calculated).

Account	AsOfDate	PrevMonthEndDate	CurrBalance	PrevBalance
1111	6/30/2018	5/31/2018	1000	1100
2222	6/30/2018	5/31/2018	2000	2200
3333	6/30/2018	5/31/2018	1500	1600

Account	AsOfDate	PrevMonthEndDate	CurrBalance	PrevBalance
1111	6/30/2018	5/31/2018	1000	1100
1111	5/31/2018	4/30/2018	1100	1200
1111	4/30/2018	3/31/2018	1200	1300
1111	3/31/2018	2/28/2018	1300	1400
1111	2/28/2018	1/31/2018	1400	1500

I have been trying to use set analysis but I am running in to some problems. I created a variable vPrevMonth with the following definition.

vPrevMonth

=MonthEnd(AddMonths([AsOfDate],-1))

Then I created a measure in the table for PrevBalance with the following formula:

=SUM({$<[AsOfDate] = {'=$(vPrevMonth)'} >} [CurrBalance])

This formula just shows the Current Balance for the AsOfDate. For example, below I just selected my AsOfDate to be 06/30/2018.

Account	AsOfDate	PrevMonthEndDate	CurrBalance	PrevBalance
1111	6/30/2018	5/31/2018	1000	1000
2222	6/30/2018	5/31/2018	2000	2000
3333	6/30/2018	5/31/2018	1500	1500

So I believe this means that in the set analysis, the {'=$(vPrevMonth)'} is searching through all possible AsOfDates and outputting a list of previous month end dates (so it is not restricted to look at only AsOfDate=06/30/2018).

Additionally, with this formula if I select two AsOfDates then it shows a PrevBalance =0. Please see below where I select AsOfDate to be either 6/30/2018 or 5/31/2018.

Account	AsOfDate	PrevMonthEndDate	CurrBalance	PrevBalance
1111	6/30/2018	5/31/2018	1000	0
1111	5/31/2018	4/30/2018	1100	0

Thank you,

Brian

[bdroesch]

Hi Sunny,

I have attached a sample of similar data to what I have been working with.  I adjusted vPrevMonth to remove the first "=" at the beginning of the equation.

Please disregard that there is no data for 06/30/2018. I have been testing with As Of Dates of 5/31/2018 or older.

Thank you,

Brian

[sunny_talwar]

Few things to note...

1) MonthEnd() gives a timestamp down to the last second of the Month... SO, MonthEnd() today will be

07/31/2018 23:59:59. But since your AsOfDate is most likely just a date, you need to use Floor to remove Time and use Date for formatting

2) You were using AsOfDate as dimension... but if you are looking to see this by selecting a single date... I would suggest you against using it as a dimension and rather use it as an expression...

see if this is what you wanted

[bdroesch]

Thanks Sunny. This does work but only if you are selecting 1 date. I guess this is a limitation if you are setting AsOfDate as an Expression.  If I make it a dimension then it will not work as currently constructed.

Any thoughts on how I could satisfy multiple date selections?

Would I need to use the AGGR() function to group by "AsOfDate"?

Thank you,

Brian

[sunny_talwar · Accepted solution]

In that case, try this

Dimension

ID

AsOfDate

Expression

=Sum([CurrBalance])

=$(vPrevMonth)

=Aggr(Above(Sum({<AsOfDate>} [CurrBalance])), ID, (AsOfDate, (NUMERIC, ASC)))

[bdroesch]

Thank you Sunny. This worked for me!

Do you mind just explaining the formula below? Specifically what {<AsOfDate>} does.

=Aggr(Above(Sum({<AsOfDate>} [CurrBalance])), ID, (AsOfDate, (NUMERIC, ASC)))

[sunny_talwar]

Let me explain with an example... when we are selecting 5/31 and 4/30.... we only have values for those two months.... but for previous month we want to see 4/30 and 3/31... in order to be able to see 3/31... we need to ignore selection in AsOfDate.... which allow Above() function to look one Month above value.

[bdroesch]

Thank you for your help Sunny!