topic Re: Set Analysis in QlikView

Set Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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])

Re: Set Analysis

sunny_talwar — Mon, 14 May 2018 16:43:17 GMT

May be one of these

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

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

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])

Re: Set Analysis

Anonymous — Mon, 14 May 2018 16:47:36 GMT

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.

Re: Set Analysis

sunny_talwar — Mon, 14 May 2018 16:48:51 GMT

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?

Re: Set Analysis

Anonymous — Mon, 14 May 2018 16:51:46 GMT

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.

Re: Set Analysis

sunny_talwar — Mon, 14 May 2018 16:53:56 GMT

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?