BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vivekk17
Calcite | Level 5

I have table like this :

 

TABLE

 

CLOSED COMMIT PIPELINE

100          50            10

.               60             70

50            .                100

 

I want to calculate another column total = closed+commit+pipeline  in VA calculated items . How to deal with the missing values , do we have any functuion like "SUM" we have in SAS area to deal with missing value .

 

Need output as :

CLOSED COMMIT PIPELINE   Total

100          50            10                160

.               60             70               130

50            .                100             150

 

I dont want to create another variable like if commit=. then 0 and use that . As in real scenario columns to add is much more that 3 and  have multiple combination.

 

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

Hi

 

It is possible to create a calculated item using an IF expression, see the picture below:

Capture.PNG

 

Here is the text for the expression:

( 
  IF ( 'CLOSED'n NotMissing )
  RETURN 'CLOSED'n
  ELSE 0 ) + ( 
  IF ( 'COMMIT'n NotMissing )
  RETURN 'COMMIT'n
  ELSE 0 ) + ( 
  IF ( 'PIPELINE'n NotMissing )
  RETURN 'PIPELINE'n
  ELSE 0 )

Yes this could be easier, but it works.

 

Bruno

View solution in original post

4 REPLIES 4
Sam_SAS
SAS Employee

Hello,

 

I don't think that what you want is possible through the Calculated Item dialog in the report builder. However, you should be able to create your Total column as part of your data query, in the Data Builder (Data Preparation) component of VA.

 

Sam

vivekk17
Calcite | Level 5

Hi Sam,

 

Thanks.

 

But am using different parameter in VA designer(applies to different column) to get weighted value out of these . And these changes based on user choice .

Thus can't move the logic to Data builder or ETL.

 

Vivek

 

 

BrunoMueller
SAS Super FREQ

Hi

 

It is possible to create a calculated item using an IF expression, see the picture below:

Capture.PNG

 

Here is the text for the expression:

( 
  IF ( 'CLOSED'n NotMissing )
  RETURN 'CLOSED'n
  ELSE 0 ) + ( 
  IF ( 'COMMIT'n NotMissing )
  RETURN 'COMMIT'n
  ELSE 0 ) + ( 
  IF ( 'PIPELINE'n NotMissing )
  RETURN 'PIPELINE'n
  ELSE 0 )

Yes this could be easier, but it works.

 

Bruno

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 4218 views
  • 0 likes
  • 3 in conversation