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

[sunny_talwar]

Can you try one of these

=Sum({$<[AsOfDate] = {"$(vPrevMonth)"}>} [CurrBalance])

or

=Sum({$<[AsOfDate] = {"$(=vPrevMonth)"}>} [CurrBalance])

[bdroesch]

Hi Sunny,

Thank you for your response. I just tried both of those options and it outputs $0.

I am also still on version 3.2 of Qilk Sense. I am not sure if there is a difference in double & single quotes on this version.

Please let me know if you have any other thoughts on how to solve this problem.

Thank you,

Brian.

[sunny_talwar]

How about giving this a shot

=Sum({$<[AsOfDate] = {"$(=$(vPrevMonth))"}>} [CurrBalance])

[bdroesch]

Unfortunately, that still outputs 0. So if I do not have the equals sign after the double or single quotations this expression outputs 0.

In my original formula, I had an equal sign after a single quote ->   {'=$(vPrevMonth)'}. 

If I put in this equals sign it outputs the current balance associated with the AsOfDate.  What exactly does the equals sign after the double or single quotations within the {} do?

[sunny_talwar]

How about if you store your expression like this

=Date(Floor(MonthEnd(AddMonths([AsOfDate],-1))), 'M/D/YYYY')

[bdroesch]

I updated the variable but that didn't seem to help with any of the possible functions that you shared with me.

I also tried to add the Date(Floor... combo to the first "AsOfDate" field that is listed in the SUM function.

Is there also a way to use the Above function? I have not used that before but was curious if that could be a solution.

[sunny_talwar]

Would it be possible for you to share a sample to check this out?

[bdroesch]

What exactly do you mean by a sample? Like the actual data that I am using?

[sunny_talwar]

Actual or mocked up data qvf file where you can show the issue