BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ChristyN
Fluorite | Level 6

SAS VA 8.5.2: I am wanting to provide my report users the option to select their view: Fiscal, Quarterly, Monthly or Daily. I currently use stacking containers for each metric to switch between the views but it is clunky. There must be away to allow the user to select the x-axis they would like. I think maybe using parameters but I am stuck. To add complexity our fiscal and quarters do not align with the calendar year so I am forced to may categories for these variables.

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
HunterT_SAS
SAS Employee

This is something I do often, and you should be able to achieve this with parameters. This blog covers the basic idea:
https://blogs.sas.com/content/sgf/2016/04/27/use-parameters-to-pick-your-metric-in-visual-analytics-...

except with a focus on measures. The high level outline of this though is the following:

- Create a custom category to cover the categorical values you want: Fiscal, Quarter, Monthly. My recommendation here is to use a completely separate datasource for this if you can. If you use the same datasource that the rest of your report uses, you run the risk of inadvertently filtering out values that might be used in your custom category. 

- Add the custom category to some prompt, say a bar chart. Add a filter so it only shows you the Fiscal, Quarter, Monthly values.

- Add a parameter to that prompt. 

- Create a calculated item with If/Then logic to return the appropriate category you want. If parameter = Fiscal then return Fiscal, etc. 

- Use that calculated item on the X axis in your graph. 

 

Hopefully that helps get you going!

View solution in original post

10 REPLIES 10
HunterT_SAS
SAS Employee

This is something I do often, and you should be able to achieve this with parameters. This blog covers the basic idea:
https://blogs.sas.com/content/sgf/2016/04/27/use-parameters-to-pick-your-metric-in-visual-analytics-...

except with a focus on measures. The high level outline of this though is the following:

- Create a custom category to cover the categorical values you want: Fiscal, Quarter, Monthly. My recommendation here is to use a completely separate datasource for this if you can. If you use the same datasource that the rest of your report uses, you run the risk of inadvertently filtering out values that might be used in your custom category. 

- Add the custom category to some prompt, say a bar chart. Add a filter so it only shows you the Fiscal, Quarter, Monthly values.

- Add a parameter to that prompt. 

- Create a calculated item with If/Then logic to return the appropriate category you want. If parameter = Fiscal then return Fiscal, etc. 

- Use that calculated item on the X axis in your graph. 

 

Hopefully that helps get you going!

KeithM
SAS Employee

What do your calculations currently look like for Fiscal Year, Fiscal Qtr, and Fiscal Month?

ChristyN
Fluorite | Level 6

Totally on board..., the issue I have now is that the month data is formatted as a date and I can't figure out how to make it categorical like my fiscal and quarter variables so the if/then statement will not work. Advice?

KeithM
SAS Employee

I think Hunter mentioned this.  You need to add another data source to your report.

1. open Excel.

Type

------

Year

Quarter

Month

2. Import in that small table and use it as the source for your prompt (which as parameter).

3. In the measure, you would use:

If MyParameter = "Year"

  return (FiscalCalculationData Item)

Else if MyParameter = "Quarter"

   return (FiscalQuarterData Item)

Else return (MonthlyData Item)

 

Note:  I'm assuming you already have the calculations representing the different Fiscal year, qtr, month.

HunterT_SAS
SAS Employee

Ah, right everything needs to be the same data type. Since Month is a Date type data item, you can convert it by creating a new calculated item and use the "Format" function. The Format function will take whatever numeric or date value you provide (Month in this case), and the corresponding format of that value, and return it as a character type. 

Then you would use that calculated item in the If/Then statement. Hopefully that gets you going!

ChristyN
Fluorite | Level 6

Thanks Hunter! But it won't let me...

ChristyN_0-1707431984764.png

 

HunterT_SAS
SAS Employee

Are you getting an error when you try to set a format and press OK? I'm not able to tell what is wrong here. Generally all you should have to do here is pick the appropriate format (guessing MONNAME in your case) and click OK, then OK again to save the calculated item, then hopefully that should be that.

ChristyN
Fluorite | Level 6

Sorry, I got distracted for a few days. I have copying the MMMYYYY variable and changing the format but it only gives me date format options. I also tried creating a new item but it gives an error:

 

ChristyN_0-1707933678882.png

Haha I'm sure it's a simple thing I am just missing!

HunterT_SAS
SAS Employee

No problem at all! 

In this case, you need to explicitly use that "Format" function within your calculated item. This function is what converts the Date type to a Character type.

Try this:
1. Create a new calculated item.

2. Click the "Text" button to switch it over to Text view instead of Visual view.
3. Copy this text in:

Format('End_Month'n[Raw], 'MMDDYY8.')

It should look something like this:

HunterT_SAS_1-1707941120967.png

 



See if that gets you closer. If you need to change the format from MMDDYY8. to something else, you certainly can. 

 

ChristyN
Fluorite | Level 6
Thank you! I love you and this community!

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 10 replies
  • 920 views
  • 2 likes
  • 3 in conversation