How to Prompt Users for Fiscal/Quarter/Monthly View

[ChristyN]

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!

[HunterT_SAS · Accepted solution]

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]

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

[ChristyN]

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]

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]

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]

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

[HunterT_SAS]

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]

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:

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

[HunterT_SAS]

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:

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

[ChristyN]

Thank you! I love you and this community!