topic Re: Using expressions inside other expressions problems. in QlikView

Using expressions inside other expressions problems.

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Using expressions inside other expressions problems.

vishsaggi — Fri, 14 Oct 2016 20:28:40 GMT

Did you try rangesum like in below link?

Range Sum

Would you mind sharing your sample app if possible ?

Thanks,
V

Re: Using expressions inside other expressions problems.

sunny_talwar — Fri, 14 Oct 2016 20:31:21 GMT

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

Re: Using expressions inside other expressions problems.

Fri, 14 Oct 2016 20:38:25 GMT

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?

Re: Using expressions inside other expressions problems.

sunny_talwar — Fri, 14 Oct 2016 21:08:21 GMT

See if this helps

Preparing examples for Upload - Reduction and Data Scrambling

Re: Using expressions inside other expressions problems.

vishsaggi — Fri, 14 Oct 2016 21:20:58 GMT

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.

Re: Using expressions inside other expressions problems.

Mon, 17 Oct 2016 13:58:59 GMT

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.

Re: Using expressions inside other expressions problems.

Mon, 17 Oct 2016 14:26:16 GMT

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!

Re: Using expressions inside other expressions problems.

sunny_talwar — Mon, 17 Oct 2016 16:43:11 GMT

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

Re: Using expressions inside other expressions problems.

Mon, 17 Oct 2016 16:51:39 GMT

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:

Re: Using expressions inside other expressions problems.

sunny_talwar — Mon, 17 Oct 2016 17:06:43 GMT

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)

Re: Using expressions inside other expressions problems.

Mon, 17 Oct 2016 17:12:55 GMT

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!

Re: Using expressions inside other expressions problems.

sunny_talwar — Mon, 17 Oct 2016 17:22:15 GMT

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

Re: Using expressions inside other expressions problems.

Mon, 17 Oct 2016 17:24:04 GMT

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?

Re: Using expressions inside other expressions problems.

sunny_talwar — Mon, 17 Oct 2016 17:43:45 GMT

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