turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Visual Analytics
- /
- sas va calculate rate between first N revenue and ...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-20-2018 12:43 PM

hi, i am new in SAS VA , i have a table like this

id revenue

1 10

2 50

3 4

4 15

5 12

6 25

the user need to choose a rank to see the first N id (eg first 3 id with max revenue: 2, 4 and 6) and have the rate between (50+15+25)/(10+50+4+15+12+25)= 0,77....

Accepted Solutions

Solution

06-27-2018
09:20 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to rufmau68

06-27-2018 09:16 AM

There are a couple ways to get what you want, but I think the most flexible way is to create a parameter 'topN_cutoff' and a calculation 'topN_revenue' like:

if 'revenue'n > 'topN_cutoff'

return 'revenue'n

else 0

Then you can create an aggregated calculation like this:

sum [_ForAll_] ('topN_revenue'n) / sum [_ForAll_] ( 'revenue'n )

The only sticky point is that you need to know what the cutoff revenue is beforehand. For any given data, it is easy to find it out with a list table and a rank applied for top 3 (or whatever rank). But I don't think VA has an operator you can use to determine it automatically. The closest would be the Percentile operator which gives you the revenue value in the data for a specified percentile, but that won't correspond to a specific numeric rank (unless, again, you already know the data and can just determine what percentile corresponds with top N).

All Replies

Solution

06-27-2018
09:20 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to rufmau68

06-27-2018 09:16 AM

There are a couple ways to get what you want, but I think the most flexible way is to create a parameter 'topN_cutoff' and a calculation 'topN_revenue' like:

if 'revenue'n > 'topN_cutoff'

return 'revenue'n

else 0

Then you can create an aggregated calculation like this:

sum [_ForAll_] ('topN_revenue'n) / sum [_ForAll_] ( 'revenue'n )

The only sticky point is that you need to know what the cutoff revenue is beforehand. For any given data, it is easy to find it out with a list table and a rank applied for top 3 (or whatever rank). But I don't think VA has an operator you can use to determine it automatically. The closest would be the Percentile operator which gives you the revenue value in the data for a specified percentile, but that won't correspond to a specific numeric rank (unless, again, you already know the data and can just determine what percentile corresponds with top N).