Using Variables in Set Analysis

[LakerLakes]

Hi Qlik Community!
I am trying to perform the Yield difference calculations. For context, I have financial data that is in hierarchy format and I have rolled up the values for Average Balances, Book Income and so on through simple aggregation functions. I have calculated the Yield for each account through following formula and saved it in master measure as "Yield".

    Sum(   {<NI -= {'?'}>} NI_VALUE)*YEAR_TO_MONTH_RATIO

/    
    Sum({<AB -= {'?'}>} AB_VALUE)

Now I want to use the values from the "Yield" (at parent account level) to calculate the Yield Difference. And I am trying out this way,

sum( {$<ACCOUNT_NAME4 = P({1<YD={'YD'}>} YD_ACCT1)>} Yield)
-
sum( {$<ACCOUNT_NAME4 = P({1<YD={'YD'}>} YD_ACCT2)>} Yield)

I though that I could use the Master measure as it is in the other measures, but I get Error in Expression.
Can someone help me in this calculation?

[Oleg_Troyansky · Accepted solution]

Hi @LakerLakes ,

The problem is caused by using an aggregation formula inside of another aggregation without using AGGR() in between. Picture it as a simple text substitution - replace the name Yield with the actual formula for yield, and you will see that you are trying to use sum on top of another sum. In order to do that, you should add an AGGR() in between and specify at what lever you want to aggregate.

So, your expression might look like this:

sum( {$<ACCOUNT_NAME4 = P({1<YD={'YD'}>} YD_ACCT1)>}

       AGGR(Yield, ACCOUNT_NAME4)

)

-

sum( {$<ACCOUNT_NAME4 = P({1<YD={'YD'}>} YD_ACCT2)>}

 AGGR(Yield, ACCOUNT_NAME4)

)

On the other hand, it looks like you just want to apply different Set Analysis conditions to the same calculation, without the need for a nested aggregation. In this case, you can probably get by with the Outer Sets and no extra sum, like this:

( {$<ACCOUNT_NAME4 = P({1<YD={'YD'}>} YD_ACCT1)>} Yield)
-
( {$<ACCOUNT_NAME4 = P({1<YD={'YD'}>} YD_ACCT2)>} Yield)

Allow me to invite you and everyone else who wants to learn advanced aggregation, set analysis, and more advanced development techniques, to the next Masters Summit for Qlik in Hamburg and to my Qlik Expert Class in Vienna. See links in my signature.

Cheers,

Oleg

[LakerLakes]

I’ve used the following expression in Qlik as a workaround, but I would appreciate it if you could help me better understand how it works. Specifically, I’m trying to grasp the evaluation logic—i.e., which parts of the expression Qlik resolves first and what the order of execution is.

sum( {$<ACCOUNT_NAME4 = P({1<YD={'YD'}>} YD_ACCT1)>}  ({<NI -= {'?'}>}   NI_VALUE)*YEAR_TO_MONTH_RATIO)
/
sum( {$<ACCOUNT_NAME4 = P({1<YD={'YD'}>} YD_ACCT1)>}  ({<AB -= {'?'}>}   AB_VALUE))

-

sum( {$<ACCOUNT_NAME4 = P({1<YD={'YD'}>} YD_ACCT2)>}  ({<NI -= {'?'}>}   NI_VALUE)*YEAR_TO_MONTH_RATIO)
/
sum( {$<ACCOUNT_NAME4 = P({1<YD={'YD'}>} YD_ACCT2)>}  ({<AB -= {'?'}>}   AB_VALUE))