Turn on suggestions

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

Showing results for

- Home
- /
- SAS Viya
- /
- Visual Analytics
- /
- Re: Creating a Year-to-go indicator on a calculated field

Options

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

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 07-28-2020 08:53 AM
(545 views)

Hi everyone!

I'm kinda new to the SAS VA and I'm not being able to create a specific calculated field.

The idea is to calculate the average revenue/coupons we'll have to make on the rest of the year to complete our year goal.

My data is on the store level, so I'll have to aggregate it to make these as big numbers. I've attached a sample of the table I'm using.

The difficult part is to calculate the amount of months left in the year to use it as the denominator. Using the revenue as example, the formula I thought was something like:

```
Sum [_ForAll_] ((
IF ( Month('anoMesData'n) <= ( Month(DatePart(Now())) - 1 ) )
RETURN 'revenueGoal2020'
ELSE . )) -
```

Sum [_ForAll_] ((
IF ( Month('anoMesData'n) <= ( Month(DatePart(Now())) - 1 ) )
RETURN 'revenue2020'
ELSE . ))/

202012 - Month(DatePart(Now()))

Is this the best way to make this calculation?

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hello!

So, a person from my area was able to help me on this problem. I only needed to put a function to aggregate the data of the denominator.

The formula that worked stayed like this:

```
( Sum [_ForAll_] ('revenueGoal2020'n) - Sum [_ForAll_] ((
IF ( Month('MONTH_YEAR'n) <= ( Month(DatePart(Now())) - 1 ) )
RETURN 'revenue2020'n
ELSE . )) ) / Max [_ForAll_] (( 12 - ( Month(DatePart(Now())) -
1 ) ))
```

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Just to clarify, the equation I'm trying to make is:

(Year Revenue Goal 2020 - YTD Revenue 2020)/(Months left on 2020)

This would be the same for the coupons

(Year Revenue Goal 2020 - YTD Revenue 2020)/(Months left on 2020)

This would be the same for the coupons

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Just updating:

I was able to put the logic on a formula, but it's giving an error because the denominator is not aggregated.

The formula I've programmed is:

```
( Sum [_ForAll_] ('revenueGoal2020'n) / Sum [_ForAll_] ((
IF ( Month('DATE'n) <= ( Month(DatePart(Now())) - 1 ) )
RETURN 'revenue2020'n
ELSE . )) ) / ( 12 - ( Month(DatePart(Now())) - 1 ) )
```

Am I doing something wrong?

Thanks!

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hello!

So, a person from my area was able to help me on this problem. I only needed to put a function to aggregate the data of the denominator.

The formula that worked stayed like this:

```
( Sum [_ForAll_] ('revenueGoal2020'n) - Sum [_ForAll_] ((
IF ( Month('MONTH_YEAR'n) <= ( Month(DatePart(Now())) - 1 ) )
RETURN 'revenue2020'n
ELSE . )) ) / Max [_ForAll_] (( 12 - ( Month(DatePart(Now())) -
1 ) ))
```

**Available on demand!**

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

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.