Solved: Re: Using expressions inside other expressions pro... - Qlik Community

Report Inappropriate Content · ‎2016-10-14

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 · ‎2016-10-17

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

View solution in original post

vishsaggi · ‎2016-10-14

Did you try rangesum like in below link?

Range Sum

Would you mind sharing your sample app if possible ?

Thanks,
V

sunny_talwar · ‎2016-10-14

May be this:

Sum(TOTAL <Child> Aggr(If(Sum(Value) > 0, Sum(Value)), Parent, Child))

Total needs to be on the Parent field

Sum(TOTAL <Parent> Aggr(If(Sum(Value) > 0, Sum(Value)), Parent, Child))

Report Inappropriate Content · ‎2016-10-14

Thanks guys. I'd love to post a sample app but this is part of a very large spreadsheet that contains sensitive information.

Is there an easy way I can just export this one tab of the app to share with with you?

sunny_talwar · ‎2016-10-14

See if this helps

Preparing examples for Upload - Reduction and Data Scrambling

vishsaggi · ‎2016-10-14

Did you try what Sunny suggested? Can you just copy some 100 rows (and mask the values) of data onto excel or use sunny's link for scrambling data and upload accordingly.

Report Inappropriate Content · ‎2016-10-17

I tried Sunny's suggestion and I just get a column full of 0s, doesn't seem to work.

I've tried to scramble and reduce the data in my app so I can post a sample, I'll reply to my original post with a sample file.

Report Inappropriate Content · ‎2016-10-17

I tried all the suggestions in the thread so far and nothing seems to be working.

I've attached a scrambled sample of my app, hopefully that helps. Thanks in advance for your help!

sunny_talwar · ‎2016-10-17

Would you be able to point out the expected output based on your attached sample?

Report Inappropriate Content · ‎2016-10-17

For instance for Parent Liqwjxyz (sorry for the scrambling) I would expect to see a -0.3 (or -0.286) for ideally both of these rows in the "Non netted" column:

Or for Parent "Lzdc pb Qmze Wyfzjc" I would expect to see a -0.5 in the Non netted column for both of these rows in the Non Netted column:

Using expressions inside other expressions problems.