Set Analysis

[Anonymous]

I am working on what i thought was a simple function.  As you can see in the chart below I am looking at categories accounts.  My first column is the sum of everything, I would like my second column to be anything that is APV and APV only, the third column is to have everything except APV.

I thought set analysis was the way to go but my numbers are not right.  Can someone let me know if i am proceeding correctly with my calculations below?

Sum([Detl Amt])

sum({$<[Account Category] = P({1<[Jnl Cd]={'APV'}>}[Account Category])>}[Detl Amt])

sum({$<[Account Category] = E({1<[Jnl Cd]={'APV'}>}[Account Category])>}[Detl Amt])

[sunny_talwar · Accepted solution]

May be one of these

1)

Sum({$<[Jnl Cd] = {'APV'}>} [Detl Amt])

Sum({$<[Jnl Cd] -= {'APV'}>} [Detl Amt])

2)

Sum({$<[Account Category] = P({1<[Jnl Cd]={'APV'}>}[Account Category]), [Jnl Cd] = {'APV'}>}[Detl Amt])

Sum({$<[Account Category] = E({1<[Jnl Cd]={'APV'}>}[Account Category]), [Jnl Cd] -= {'APV'}>}[Detl Amt])

[Anonymous]

Thank you Sunny;

  Im not sure where i went wrong, i have not seen the -=, which i will assume is equivalent to <> in SQL?

How do you know when to extend the analysis to include that syntax.  It looks like you put a set analysis inside a set analysis.

[sunny_talwar]

i will assume is equivalent to <> in SQL?

This is right

How do you know when to extend the analysis to include that syntax.  It looks like you put a set analysis inside a set analysis.

I am not sure I follow your question.... which of the two expressions worked for you?

[Anonymous]

crazy enough, both sets yielded the same results.

So I see on my example i sent i over thought it as the first set worked.

But the second set worked as well and I am trying to understand why that also worked.  I like that the first set worked, just curious as maybe the second set may come in handy for other analysis.

[sunny_talwar]

Based on different data sets and requirements the two set might give different answers. Do you have single or multiple Jnl Cd per Account Category?