This website is not affiliated with, sponsored by, or approved by SAP AG.

Remove positive values from Query

Business Warehouse

Moderators: Snowy, thx4allthefish

Remove positive values from Query

Postby Triskel » Mon Nov 29, 2010 4:50 am

Hi All,

I have a Bex Query where I am trying to display 0 for a key figure where the amount is greater than 0, showing only the negative amounts.

This query is fed by an InfoCube, transformed from an ODS. So I could definitely create a new Key Figure InfoObject and do it in a formula within the transformation, but I was wondering if there was a way to avoid creating an InfoObject and going through all that trouble by applying some simple logic within the Bex.

Especially that I need to do the reverse as well, meaning showing only the positive values in another Key Figure.

Cheers,
Phil
Triskel
 
Posts: 14
Joined: Mon Jun 28, 2010 7:52 am
Location: France

Re: Remove positive values from Query

Postby 2bwornot2bw » Thu Dec 09, 2010 2:44 pm

it depends on what aggregations level you want to do this at. if it is at whatever the query is displaying just create a calculated key figure using in min function of zero and your basic key figure; positives will go to zero and negatives will show (use max function for positive values). if it needs to be at the raw data granularity you have a non-linear aggregation problem (similar to displaying absolute values), so... add a characteristic to your InfoProvider, during the load transformation set it to "N" if the key figure is negative and "P" if it is positive (and zero). that way it is one key figure for standard reports; filter on the "P" for positive reports, and filter on the "N" for negative reports (if you don't like N and P you can use anything, I just think N's and P's get neglected in todays society)
when in danger or in doubt, run in circles, scream and shout.
2bwornot2bw
 
Posts: 127
Joined: Mon Aug 09, 2004 10:56 am
Location: you ess eh

Re: Remove positive values from Query

Postby Triskel » Fri Feb 25, 2011 3:59 am

Thanks 2bw, option 1 is what I was after as I wanted to avoid option 2. I have managed by creating a calculated key figure within the Bex query.
Triskel
 
Posts: 14
Joined: Mon Jun 28, 2010 7:52 am
Location: France


Return to Business Warehouse

Who is online

Users browsing this forum: No registered users and 1 guest





This website is not affiliated with, sponsored by, or approved by SAP AG.