topic Re: Set Analysis: Need To Look At Data From Previous Month in App Development

Set Analysis: Need To Look At Data From Previous Month

bdroesch — Fri, 06 Jul 2018 15:08:05 GMT

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

Re: Set Analysis: Need To Look At Data From Previous Month

sunny_talwar — Fri, 06 Jul 2018 15:43:20 GMT

Can you try one of these

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

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

Re: Set Analysis: Need To Look At Data From Previous Month

bdroesch — Fri, 06 Jul 2018 15:51:06 GMT

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.

Re: Set Analysis: Need To Look At Data From Previous Month

sunny_talwar — Fri, 06 Jul 2018 16:05:26 GMT

How about giving this a shot

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

Re: Set Analysis: Need To Look At Data From Previous Month

bdroesch — Fri, 06 Jul 2018 16:45:30 GMT

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?

Re: Set Analysis: Need To Look At Data From Previous Month

sunny_talwar — Fri, 06 Jul 2018 16:51:02 GMT

How about if you store your expression like this

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

Re: Set Analysis: Need To Look At Data From Previous Month

bdroesch — Fri, 06 Jul 2018 16:59:32 GMT

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.

Re: Set Analysis: Need To Look At Data From Previous Month

sunny_talwar — Fri, 06 Jul 2018 18:12:38 GMT

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

Re: Set Analysis: Need To Look At Data From Previous Month

bdroesch — Fri, 06 Jul 2018 20:17:40 GMT

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

Re: Set Analysis: Need To Look At Data From Previous Month

sunny_talwar — Sat, 07 Jul 2018 02:20:57 GMT

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

Re: Set Analysis: Need To Look At Data From Previous Month

bdroesch — Mon, 09 Jul 2018 22:21:14 GMT

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

Re: Set Analysis: Need To Look At Data From Previous Month

sunny_talwar — Mon, 09 Jul 2018 23:30:45 GMT

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

Re: Set Analysis: Need To Look At Data From Previous Month

bdroesch — Tue, 10 Jul 2018 19:17:05 GMT

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

Re: Set Analysis: Need To Look At Data From Previous Month

sunny_talwar — Tue, 10 Jul 2018 19:21:30 GMT

In that case, try this

Dimension

AsOfDate

Expression

=Sum([CurrBalance])

=$(vPrevMonth)

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

Re: Set Analysis: Need To Look At Data From Previous Month

bdroesch — Tue, 10 Jul 2018 22:53:27 GMT

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)))

Re: Set Analysis: Need To Look At Data From Previous Month

sunny_talwar — Wed, 11 Jul 2018 00:40:38 GMT

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.

Re: Set Analysis: Need To Look At Data From Previous Month

bdroesch — Fri, 13 Jul 2018 17:54:35 GMT

Thank you for your help Sunny!