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.

[kdmarkee]

Not sure I understand the question, so I'm not sure if this answers it.  The user selects a Service Year (ie, 2016, 2017) and a Month (ie, Jan, Feb) and I build the date in my variables which is always going to be the first of the month.  The Service Year is tied to my data model (star schema) and my Month is a data island not tied to the data model, and used to just build a date.

[sunny_talwar]

Okay, is _diMonthName used as a dimension when you check this? Would you be able to share a sample to test it out?

[kdmarkee]

I'll dummy up an app with sample data;  have personal info due to working with health data.  Will respond back when I have that...

[kdmarkee]

I decided to try this as well as my expression and I've narrowed it down to the fact that it does not like the -1.  So I'll keep playing with this and see if there is another way to accomplish that.    Sum({$<Date={"$(='<'& MakeDate(if(_diMonthNum = 1, [Service Year], [Service Year]-1), _diMonthNum, 1))"}, GROUP_KEY={$(=vGroup_A)}, Group=>}[Contracted Amount]).The other expression works because I don’t use that, rather it has a +1, which it doesn’t seem to mind.

[sunny_talwar]

That was sort of where I was getting at... try to wrap it around with () and see if it works

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

[kdmarkee]

Here's an application to experiment with.  Thanks.

[sunny_talwar · Accepted solution]

This seems to have worked for me

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

[sunny_talwar]

Alternatively, create the variable vYearPartPrior like this

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

and then use your current expression as is

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

[kdmarkee]

I still have to do some more testing but this did return results, thanks.  I see you changed this portion, [Service Year]={'$(=$(vYearPartPrior))'} . Our app is super over engineered with tons of crazy date stuff, and combine that with all the symbol mumbo jumbo (‘, $, (), =, etc) and this stuff gets overwhelming quickly.   Then after some testing of actual values/results on my end I realized I’m using the wrong year here! 

So the expression that is actually correct after some spot checking is:

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

Thanks again for your help, I very much appreciate it.