topic Re: Issue with storing date as a variable to be used in set analysis in App Development

Issue with storing date as a variable to be used in set analysis

Fri, 03 Feb 2017 06:29:38 GMT

I have created a variable to store the prior month that user selects.

vPriorMonth = Date(addmonths(MonthStart(SnapDate),-1),'MMM-YY')

Purpose is to show the previous month's balance if a month is selected.

eg. User select SnapDate = Jul-16

The following expression will generate these outputs

$(vPriorMonth) => Jun-16

Sum({$<SnapDate=, SnapDate={'Jun-16'}>}MonthEndBalance) => The correct balance if I hardcoded it

Sum({<SnapDate=, SnapDate={'$(vPriorMonth)'}>}MonthEndBalance) => No results

Sum({<SnapDate=, SnapDate={$(=vPriorMonth)}>}MonthEndBalance) => => No results

I have narrowed it down to the issue is that it is a date field as I have replaced the date check with a unrelated check and the code works fine.

Is there any way to get the set analysis to work?

Additional info, not too sure if it helps.

The date format in the load editor:

SET DateFormat='DDMMMYYYY';

How SnapDate is loaded:

Date(MonthStart(SNAP_DATE),'MMM-YY') AS SnapDate

Re: Issue with storing date as a variable to be used in set analysis

sunny_talwar — Fri, 03 Feb 2017 10:19:56 GMT

How about this:

Sum({<SnapDate=, SnapDate={"$(=vPriorMonth)"}>}MonthEndBalance)

Re: Issue with storing date as a variable to be used in set analysis

Mon, 06 Feb 2017 01:33:04 GMT

That doesn't return any result as well.

Re: Issue with storing date as a variable to be used in set analysis

rahulpawarb — Mon, 06 Feb 2017 10:10:48 GMT

Hello Melvin,

Could you please use below given sample code snippet:

//Definition of vPriorMonth
Date(addmonths(MonthStart(Max(SnapDate)),-1),'MMM-YY')

//Expression to calculate Previous Month Balance
Sum({<SnapDate=, SnapDate={"=$(vPriorMonth)"}>}MonthEndBalance)

Hope this will help!

Regards!

Rahul

Re: Issue with storing date as a variable to be used in set analysis

sunny_talwar — Mon, 06 Feb 2017 10:14:45 GMT

How about if you remove the first SnapDate... I should have seen this before and removed that...

Sum({<SnapDate={"$(=vPriorMonth)"}>}MonthEndBalance)

Re: Issue with storing date as a variable to be used in set analysis

Mon, 06 Feb 2017 10:48:08 GMT

//Expression to calculate Previous Month Balance

Sum({<SnapDate=, SnapDate={"=$(vPriorMonth)"}>}MonthEndBalance)

This gives the current month's balance instead of prior months balance. Could it be that the expression is not evaluated?

Re: Issue with storing date as a variable to be used in set analysis

Mon, 06 Feb 2017 10:50:01 GMT

Sum({<SnapDate={"$(=vPriorMonth)"}>}MonthEndBalance)

After removing the first SnapDate, expression is still returning 0.

Re: Issue with storing date as a variable to be used in set analysis

rahulpawarb — Mon, 06 Feb 2017 13:02:11 GMT

Hello Melvin,

Could you please share the application with sample data? This will help us to do the root cause analysis.

Regards!

Rahul

Re: Issue with storing date as a variable to be used in set analysis

sunny_talwar — Mon, 06 Feb 2017 22:15:32 GMT

Would you be able to share a sample to look at this?

Re: Issue with storing date as a variable to be used in set analysis

Tue, 07 Feb 2017 03:56:10 GMT

I have attached a sample to the original post.

Re: Issue with storing date as a variable to be used in set analysis

jonathandienst — Tue, 07 Feb 2017 05:15:48 GMT

Are you using SnapDate as a dimension and you are hoping to get the prior month by row?

If so, you cannot use a set expression - the set expression is evaluated for the table as a whole, and not on a row by row basis. For this case, use a Sum(If(..)) expression or create a derived prior month field in your load script.

If not, use an aggregate function like Max(..) in your variable definition to ensure that it can be evaluated at a global level.

Als, you cannot refer to the same field twice in the set expression. If you do, the first refreence will be ignored. So the first reference to SnapDate in Sum({<SnapDate=, SnapDate={'$(vPriorMonth... will be ignored and clutters your expression for no purpose.