Set analysis Question - Formula working but is needs a change

[Ja123__]

Hi Guys

My measure below is working except for the last multiply by months (datediff) step. Datediff is difference between Start and End date variables.

Sometimes a member only joins(effective) later than start data, hence the IF statement.

Sum(
{
<txtDescription12INDICATOR = {'AVC'}, dteEffectiveDate11 = {[<=$(vEndDate)]}, blnRepeat = {'1'},dteTerminated11INDICATOR = {'-1'}, ExitTypeINDICATOR = {'-1'}>
+
<txtDescription12INDICATOR = {'AVC'}, dteEffectiveDate11 = {[<=$(vEndDate)]}, blnRepeat = {'1'},dteTerminated11 = {[>$(vEndDate)]}, ExitTypeINDICATOR = {'-1'}>
}

if(isnull(numAmount),0,numAmount)

)

*

/*Times the result by number of months applicable. Either month diff between start and end date or this must be the shorter effective date to End Date. */

if(
round((Date(Date#('$(vEndDate)','YYYY/MM/DD')) - Date(Date#(dteEffectiveDate11, 'YYYY/MM/DD'))) / 30.42)
<
$(vMonthDiff)
,
round((Date(Date#('$(vEndDate)','YYYY/MM/DD')) - Date(Date#(dteEffectiveDate11, 'YYYY/MM/DD'))) / 30.42)
,
$(vMonthDiff)
)

At first the results seemed right but then what I have noticed now is sometimes the month diff that is being used in the calc is from a row of data that does not meet that first bit of criteria in the operators!?

What I experimented with is adding those operators into Round/DateDiff section but no success yet!

Here is a snap of one member, you will notice the issue with the number of months and the problem I am dealing with. The smaller number 46305 is correct. The other is taking num amount and x 4. Instead of 3?!

These two rows show two RULE rows for the member. So I'm not sure why it uses the datediff from the other RULE row when it aggregates for the member...

Kind regards,

James

[Ja123__ · Accepted solution]

Found my solution. Hopefully helps someone else.... (And isn't too much of a mouthful)
I was forgetting to add '{''s so they were not working.

Check for the same operators as the sum part in the days part. And now it is displaying the right amount in line and in total for the column.

Sum(
{
<txtDescription12INDICATOR = {'AVC'}, dteEffectiveDate11 = {[<=$(vEndDate)]}, blnRepeat = {'1'},dteTerminated11INDICATOR = {'-1'}, ExitTypeINDICATOR = {'-1'}>
+
<txtDescription12INDICATOR = {'AVC'}, dteEffectiveDate11 = {[<=$(vEndDate)]}, blnRepeat = {'1'},dteTerminated11 = {[>$(vEndDate)]}, ExitTypeINDICATOR = {'-1'}>
}

if(isnull(numAmount),0,numAmount)

)

*
if(

min(
{
<txtDescription12INDICATOR = {'AVC'}, dteEffectiveDate11 = {[<=$(vEndDate)]}, blnRepeat = {'1'},dteTerminated11INDICATOR = {'-1'}, ExitTypeINDICATOR = {'-1'}>
+
<txtDescription12INDICATOR = {'AVC'}, dteEffectiveDate11 = {[<=$(vEndDate)]}, blnRepeat = {'1'},dteTerminated11 = {[>$(vEndDate)]}, ExitTypeINDICATOR = {'-1'}>
}

round((Date(Date#('$(vEndDate)','YYYY/MM/DD')) - Date(Date#(dteEffectiveDate11, 'YYYY/MM/DD'))) / 30.42))
<
$(vMonthDiff)
,
min(
{
<txtDescription12INDICATOR = {'AVC'}, dteEffectiveDate11 = {[<=$(vEndDate)]}, blnRepeat = {'1'},dteTerminated11INDICATOR = {'-1'}, ExitTypeINDICATOR = {'-1'}>
+
<txtDescription12INDICATOR = {'AVC'}, dteEffectiveDate11 = {[<=$(vEndDate)]}, blnRepeat = {'1'},dteTerminated11 = {[>$(vEndDate)]}, ExitTypeINDICATOR = {'-1'}>
}
round((Date(Date#('$(vEndDate)','YYYY/MM/DD')) - Date(Date#(dteEffectiveDate11, 'YYYY/MM/DD'))) / 30.42))
,
$(vMonthDiff)
)