Set Analysis

[bvann040684]

I'm trying to get all this Set Analysis down but I'm still struggling.
How would I change this if statement to set analysis?

Sum ( if(Allocation_Year = $(=Only(d_Year)) And Allocation_Month = '$(=Only(d_Month))'

,

if (NOI_TRN_NUMBER = 0 And (V_STATUS <= 'U' Or VEX_V_CHARGE > 0 or (V_STATUS >= 'X' And V_DATE_ENTERED <> NOI_ALLOC_DATE)), VEX_V_UNITS

,

if(NOI_TRN_NUMBER = 0 And V_STATUS >= 'X' And (V_DATE_ENTERED = NOI_ALLOC_DATE Or VEX_V_CHARGE < 0), VEX_V_UNITS * -1

,

0))

,

0))

Any help would be a great help, thanks.

[#2]

I'm not sure if this can be completely recreated in Set Analysis. Sometimes, you need to use If statements. For example, you can recreate V_DATE_ENTERED = NOI_ALLOC_DATE in Set Analysis if both of those are fields. If V_DATE_ENTERED is a variable, then you can reverse it and do NOI_ALLOC_DATE={'$(V_DATE_ENTERED)'} as part of your Set Analysis. The clauses that use V_DATE_ENTERED are the only ones that immediately jump out at me. If that value is a variable, then you should be able to recreate it in Set Analysis, but the nested and/or logic is going to get tricky.

Are you running into performance issues?

Start with:

Sum({<Allocation_Year={$(=Only(d_Year))},Allocation_Month={'$(=Only(d_Month))'}>}
if (NOI_TRN_NUMBER = 0 And (V_STATUS <= 'U' Or VEX_V_CHARGE > 0
 or (V_STATUS >= 'X' And V_DATE_ENTERED <> NOI_ALLOC_DATE)), VEX_V_UNITS
,
if(NOI_TRN_NUMBER = 0 And V_STATUS >= 'X' And (V_DATE_ENTERED = NOI_ALLOC_DATE
 Or VEX_V_CHARGE < 0), VEX_V_UNITS * -1
,
0))
)

If you can verify that V_DATE_ENTERED is a variable, then I'll take a shot at the more complicated Ifs.

[bvann040684]

Its running a little slow but nothing I would consider to be an issue.

What I'm trying to do is set up a table that after selecting a month and a year gives me that months total (lets say sales) MTD and YTD. I was told I needed to change that formula to set analysis before that would work.

[#4]

If you're not experiencing slow downs with the original expression, then I wouldn't attempt to convert the whole thing to Set Analysis. If you use my expression, that would eliminate one if statement and handle your month and year selections as part of the Set Analysis. I think in this case, leaving the last two ifs as if statements may be the best idea.

You should be able to create an expression that calculates the month total and year total based on month and year selections without Set Analysis. Many times (most?) what can be done in Set Analysis can be done using an If statement. Set Analysis is more effecient. That's why I say if you're not experiencing issues with the current expression, it may not be worthwhile to spend the time changing it to Set Analysis (if that is even possible).