Set Analysis Formula Issues

[#1]

I want to implement this formula:-

If(GLNormalBalance <> 'D',Sum(GLProfitLossTranAmount),-Sum(GLProfitLossTranAmount))

But with this set analysis statement :-

{$<Month={$(=Only(Month))}>}

How do I combine the two?

NB: this is NOT the way to do it :-

Sum({$<Month={$(=Only(Month))}>} IF(GLNormalBalance <> 'D',GLProfitLossTranAmount,-GLProfitLossTranAmount))

I need to negative sum and positive sum for the correct total at the bottom of the chart.

[eiconsulting]

Why instead of using an IF statement every time, you don't set the amount negative with the LOAD? It could also be faster.

I'll also try to solve it your way if I can find a test "environment"...

Flavio

[#3]

Thanks for your reply Flavio.

The reason why I am not using the script is because i want to change the types of summations which take place, not the presentation of the data (that is easy and I have already done that in the script).

You can see this example in action below. By using the IF statement

If(GLNormalBalance <> 'D',Sum(GLProfitLossTranAmount),-Sum(GLProfitLossTranAmount))

I can reverse the summations which occur depending on the GLNormalBalance field. In this case Revenue MINUS COGS, MINUS Expenses. Rather than a simple straight sum of the column.

		Account Type Mth Act If Statement Revenue 818,633 818,633 COGS 102,064 102,064 Expenses 600,810 600,810 TOTAL 1,521,508 115,759

[eiconsulting]

I see what you want to do... I tested the practical difference between the following expressions (let aside for a moment the SA)

it is a sophisticated way to obtain something that I would try to do with the "presentation" leaving the algebric sum straight.

What happens if you have one amount in the COGS that is a reversal? I have not tested it so extensively.

Beside being curious I cannot help you, sorry.

Flavio

If(GLNormalBalance <> 'D',Sum(GLProfitLossTranAmount),-Sum(GLProfitLossTranAmount)) *** look like it's doing all positive until

there is a totally uniform "D" selection

and

sum(If(GLNormalBalance <> 'D',GLProfitLossTranAmount,-GLProfitLossTranAmount)) *** straight sum

[#5]

Couldn't you use:

If(GLNormalBalance <> 'D',Sum({$<Month={$(=Only(Month))}>}GLProfitLossTranAmount),
-Sum({$<Month={$(=Only(Month))}>}GLProfitLossTranAmount))

Or if you want something shorter, try:

If(GLNormalBalance <> 'D', 1, -1) * Sum({$<Month={$(=Only(Month))}>}GLProfitLossTranAmount)

EDIT: Oops, I guess you want the If evaluated for every case instead of only the first one. I have an idea, but I'll test it on a sample first.

EDIT 2: Try this:

Sum({$<GLNormalBalance = {'D'}, Month={$(=Only(Month))}>} -GLProfitLossTranAmount) +
Sum({$<GLNormalBalance -= {'D'}, Month={$(=Only(Month))}>}GLProfitLossTranAmount)

So much for being shorter, but I tested it with some sample data and it seemed to work.

[johnw]

Matt Sweeney wrote:{$<Month={$(=Only(Month))}>}

Reading your set analysis statement literally, it says "If one and only one month is selected, then use the current selections as the set, otherwise return 0". The current selections don't require set analysis, and neither does 0. You can just write it with an IF:

If(Only(Month),If(GLNormalBalance<>'D',Sum(GLProfitLossTranAmount),-Sum(GLProfitLossTranAmount)),0)

So I'm probably not understanding what you want to do.