SAS Visual Analytics Report Creating Last Year KPI's

[fda2]

Hi,

I have a report that contains sales dataset. I have sales over years. I want to select a year from page prompt and see my list table showing selected year's and its previous year's total sales data in 2 different columns. I tried to create a parameter but failed to combine with a KPI. How is it possible?

[Stu_SAS · Accepted solution]

Hey @fda2! Here is one way to go about it. Let's say your data looks like this:

Year	Sales
2021	$1000
2022	$2000
2023	$2500
2024	$4000

You can use a parameter, calculated item, and a list table to achieve this. Here's how you do it:

1. Create a new numeric parameter called "Year Select"
2. Create a new calculated column called "Prior Year Sales":
   

   if 'Year'n = 'Year Select'p-1 return 'Sales'n
       else .

   This will set every value to missing if it is not the prior year that is selected from the drop-down list control. Otherwise, it will populate the value. This is what we want, becuase SAS will return only the non-missing value.
3. Create another calculated item and call it "Selected Year Sales":
   

   if 'Year'n = 'Year Select'p return 'Sales'n
       else .

4. Create a new drop-down list control on canvas and add "Year" as the category and "Year Select" as the parameter. Set the drop-down list as required, but do not add any actions to the drop-down list.
5. Add a new list table to canvas and add "Selected Year Sales" and "Prior Year Sales" as the columns.

You're done! When you select a year, the calculated item updates and your list table displays the prior year's value.

How does this work?

Let's go behind the scenes. The parameter is taking on a value based on what is selected in the drop-down list control. When you select 2022, your calculated columns are updating like this in the background:

Year	'Year Select'p	'Selected Year Sales'n	'Prior Year Sales'n
2021	2022	.	$1000
2022	2022	$2000	.
2023	2022	.	.
2024	2022	.	.

SAS will try to sum all of these values. Missing values are excluded from the sum, leaving only $2000 and $1000 left. Therefore, the returned values in the list table are $2000 for 2022 and $1000 for 2021.

An alternative way

One simpler way would be to use a crosstab and add the following filter to it:

In('Year'n, 'Year Select'p, 'Year Select'p-1)

This will filter the crosstab down to the selected year and the prior year:

You can also use Key Values with a lattice category this way, too!

[fda2]

Hi  @Stu_SAS,

Thanks for the great solution and comprehensive expression! Your solution worked. I have a question about key-value pairs. You named KPI's as "Selected Year Sales" and "Prior Year Sales". How did you show only the name of "Sales" in your key-value pair cards? And how did you show the year value differently in key-value cards?

[Stu_SAS]

The reason for this is because I used the Sales variable within the key value rather than the calculated items, "Current Year Sales" and "Prior Year Sales." Since the key value is using a paramaterized filter with a lattice group of "Year", we do not need to use the calculated items to identify the selected year and the prior year.

[fda2]

Alright got it, thanks!