Using variables in set analysis

[kdmarkee]

I have a scenario where I am using the script to generate a lot variables.  Below is the resulting text for just a small subset of those variables as seen in the Variable Overview:

--vYearPartCurr

if(_diMonthNum = 1, [Service Year]+1, [Service Year])

--vYearPartPrior

if(_diMonthNum = 1, [Service Year], [Service Year]-1)

--vExpCurr_A

Sum({$<Date={"$(='<'& MakeDate($(=vYearPartCurr), _diMonthNum, 1))"}, GROUP_KEY={$(=vGroup_A)}, Group=>}[Contracted Amount])

--vExpPrior_A

Sum({$<Date={"$(='<'& MakeDate($(=vYearPartPrior), _diMonthNum, 1))"}, GROUP_KEY={$(=vGroup_A)}, [Service Year]={$(=vYearPartPrior)}, Group=>}[Contracted Amount])

vYearPartCurr and vYearPartPrior represent the correct years when I test them in a Text Box object.

The expression vExpCurr_A shows the correct values.

The problem I am having is that I do not get any values for the expression using vExpPrior_A.  The expressions are basically the same except this one has an addition filter on [Service Year], and even if I remove that portion from the set analysis, it still is not working.

I just don't understand why the behavior is different between my 2 expressions of vExpCurr_A and vExpPrior_A.  I'm hoping someone can see something I don't or perhaps even offer a different/better approach.  Thanks.

[sunny_talwar]

Have you checked the variable overview to make sure that the expression still looks the same after the reload? The reason I ask this is because $ sign expansion get evaluated in the script and sometimes you would see parts of the expressions missing from your variable.

[kdmarkee]

The syntax posted in the thread was actually copied from the Variable Overview.  So with that said, even if I just test my working expression (ie, variable vExpCurr_A) with the full syntax which is Sum({$<Date={"$(='<'& MakeDate($(=vYearPartCurr), _diMonthNum, 1))"}, GROUP_KEY={$(=vGroup_A)}, Group=>}[Contracted Amount]), it works. 

Now, if I take that same syntax but replace vYearPartCurr with vYearPartPrior, and just test that much of it (without the Service Date filter in the set analysis), that does not work.  That baffles me because both vYearPartCurr with vYearPartPrior give me the correct value in a text box object, but I don't understand why set analysis likes it for the vExpCurr_A expression but not vExpPrior_A.

Hopefully that makes sense.

[sunny_talwar]

May be try to remove the = sign within the dollar sign expansion:

Sum({$<Date={"$(='<'& MakeDate($(vYearPartCurr), _diMonthNum, 1))"}, GROUP_KEY = {$(=vGroup_A)}, Group=>}[Contracted Amount])

[Anil_Babu_Samineni]

So, you are trying with this?

Sum({$<Date={"$(='<'& MakeDate(if(_diMonthNum = 1, [Service Year]+1, [Service Year]), _diMonthNum, 1))"}, GROUP_KEY={$(=vGroup_A)}, Group=>}[Contracted Amount])

Can you try this in Text object and show the result?

MakeDate(if(_diMonthNum = 1, [Service Year]+1, [Service Year]), _diMonthNum, 1)

[kdmarkee]

Yes, that is basically what is occurring. And the result I get from the MakeDate statement (my “current date” scenario) is a correct date like this:  1/1/2018.  If I run the other one (my “prior date” scenario) , MakeDate(if(_diMonthNum = 1, [Service Year], [Service Year]-1), _diMonthNum, 1), that also gives me a correct date, ie 1/1/2017.

[Anil_Babu_Samineni]

What is that Date format in your field?

[kdmarkee]

I've tried with and without the "=" and with and without single quotes, but I don't see why I'd have to when I’m running the same expression but with 2 different variables that both seem to provide the correct date. 

How is it that I can run this expression with success:

Sum({$<Date={"$(='<'& MakeDate($(=vYearPartCurr), _diMonthNum, 1))"}, GROUP_KEY={$(=vGroup_A)}, Group=>}[Contracted Amount]),

But this one is not successful:

Sum({$<Date={"$(='<'& MakeDate($(=vYearPartPrior), _diMonthNum, 1))"}, GROUP_KEY={$(=vGroup_A)}, Group=>}[Contracted Amount])?

I’m totally stumped.

[sunny_talwar]

I see what might be going on... to test my theory, can you select _diMonthNum = 1. When you do this, I expect that the first expression (which worked previously) will not work anymore. and the second expression (with did not work, will start working). Can you check if my theory is right or not?

[kdmarkee]

It doesn't matter what _diMonthNum is selected, my current date expr always works and the prior date expr never works. And I doubt it matters but I'll mention that I have a data island with fields _diMonthNum and _diMonthName, where the user selects the _diMonthName (ie, Jan, Feb,) and behind the scenes I am using _diMonthNum to build a date.