Using expressions inside other expressions problems.

[#1]

Hi All, I'm new to Qlikview and this is my first post. I've found this forum super useful so far so hopefully you can help!

I have Data in a pivot table that looks roughly like the below, and I'm having a really frustrating time trying to use the values of previous expressions in later ones.

I have a column of Values, and then another expression "OnlyNegValues" that uses an if statement to grab only the negative values (change any positive value to 0). This part works fine.

I am trying to create a column that sums the "OnlyNegValues" column for each parent, which i thought should be really simple but I feel like I've tried everything and can't get it.

I thought it should be some simple formula like =Aggr(sum(OnlyNegValues), Parent) but that doesn't work. I've tried a bunch of different things, sometimes i can get it to sum up ALL of the values as I show here (not just the negative ones) other times I get just an entire column of zeros.

Any help you have is greatly appreciated!

Parent	Child	Values	OnlyNegValues	Sum of Negative Values - WRONG	DESRIED
P	C1	$1.4	$0.00	-
P	C2	$0.5	$0.00	-
P	C3	($0.6)	($0.63)	-
P	C4	($2.2)	($2.21)	-1.0031640270963	-2.84
P2	C5	$1.0	$0.00
P2	C6	($0.5)	($0.50)	0.5	-0.5

[sunny_talwar · Accepted solution]

This?

Expression:

=Sum(Total <Parent> Aggr(if(((Sum({$<Counterparty_SECFIN-={"$('-'))"},Scenario_Name={'Risk Off'}>} CV_dirty_MV_PC)

-

Sum({$<CV_reserve_mark-={'(none)'},Scenario_Name={'Risk Off'},CV_Instrument_type={'Bond','ABS','Index Bond','CP'}>} if(CV_collateral=Forward_Issue_ID, Forward_Issue_MV,0))

+

Sum({$<Counterparty_SECFIN-={"$('-'))"},Scenario_Name={'Risk Off'}>} LST_scenario1_total_PL)

-

Sum({$<CV_reserve_mark-={'(none)'},Scenario_Name={'Risk Off'},CV_Instrument_type={'Bond','ABS','Index Bond','CP'}>} if(CV_collateral=Forward_Issue_ID, Forward_Issue_Underlying_PNL,0)))/1000000000)>0,0, ((Sum({$<Counterparty_SECFIN-={"$('-'))"},Scenario_Name={'Risk Off'}>} CV_dirty_MV_PC)

-

Sum({$<CV_reserve_mark-={'(none)'},Scenario_Name={'Risk Off'},CV_Instrument_type={'Bond','ABS','Index Bond','CP'}>} if(CV_collateral=Forward_Issue_ID, Forward_Issue_MV,0))

+

Sum({$<Counterparty_SECFIN-={"$('-'))"},Scenario_Name={'Risk Off'}>} LST_scenario1_total_PL)

-

Sum({$<CV_reserve_mark-={'(none)'},Scenario_Name={'Risk Off'},CV_Instrument_type={'Bond','ABS','Index Bond','CP'}>} if(CV_collateral=Forward_Issue_ID, Forward_Issue_Underlying_PNL,0)))/1000000000)), Parent, Counterparty_SECFIN))

[#3]

Yes! Thank you that seems to work. This looks like roughly the same thing you posted previously. The only difference is that you're using the full formula here for "Values" instead or just the expression name "Values."

Is there a reason why i need to put this entire complicated formula within the Aggr() brackets? To me it seems like I should just be able to use something like the below, instead of including that entire long formula within the Aggr() function.

=Sum(Total <Parent> Aggr(OnlyNegValues)), Parent, Counterparty_SECFIN))

Why do i have to keep using this super long formula? why can't i just use the previous expression name that already contains that formula? Am I missing something here?

Thanks again!

[sunny_talwar]

Unfortunalety, Aggr() function do not work with column names. So whenever you have used Aggr() you will have to use the complete expression .

[#5]

Ahhhh ok thanks Sunny, that explains a lot about why I was struggling so much with this haha.

Is that true only of the Aggr() function? Are there any other functions where column names are off limits?

[sunny_talwar]

Aggr() is the only one that comes to mind right now. But if I hear about something, I will come back to update this post