Spotfire Bargraph to show Avg Quality by year for previous years and by month for current year

Question

I have a bargraph which shows the quality of my data for each month. As the data is growing over the years, the graph is getting messy. So, i would like to show Avg Quality by year for all those months in Previous years but monthly for the current year.

Can any one help me how to achieve this.

Thanks, -Dileep

Answer 1

This needs to be separated into two charts, or it just won't be legible. I'd suggest:

1. Creating an AVG by Year bar chart graph for all your data
2. Creating a details visualization of the above Yearly Average data which shows the 12 months of that year with SUM() as your aggregation method. Be sure to have YEAR > Month on your category axis

Thus, if you need to compare 1, 2, 3 or all the years by month you can select them all, or just the ones you want.

Answer 2

I was able to do it with the help of some calculated columns. To help explain, let's assume the value column in your data set is called Value and the date column in your data set is called ValueDate. I created some fictional data that extends into April, 2017 to help illustrate the solution.

1. Insert a calculated column to classify whether or not the date in each row is in the current year or not. I'm going to call the column DateGrouping.

   CASE WHEN Year(DateTimeNow())=Year([ValueDate]) THEN "Monthly" WHEN Year(DateTimeNow())>Year([ValueDate]) THEN "Yearly" END AS [DateGrouping]

2. Insert a calculated column that tells us the last date of the month for ValueDate. E.g., if the date in the row is 4/10/2015, the last day of the month for the day in that row is 4/30/2015. If the date in the row is 7/8/2013, the last day of the month for the date in that row is 7/31/2013. We'll call this column LastDateOfMonth.

   DateAdd("day",-1,date(Year([ValueDate]),Month(DateAdd("month",1,[ValueDate])),1))

3. Insert a calculated column that tells us the last date of the year for ValueDate. Of course, it's just Dec. 31 of ValueDate's year. Call this column LastDateOfYear.

   Date(Year([ValueDate]),12,31)

4. Finally, we utilize the previous three calculated columns to add a calculated column for the date value we want to plot. You are wanting to plot by month for the current year, and by year for prior years.

   CASE [DateGrouping] WHEN "Monthly" THEN [LastDayOfMonth] WHEN "Yearly" THEN [LastDateOfYear] END

On my value axis is the Value column, which I'm aggregating as an Average. On my category axis is the calculated DatePlot column -- I set the category axis to Categorical Scale. And I'm coloring by the calculated DateGrouping column to offer a visual cue that the data is aggregated differently over time.

Now, should this be done? There are certainly some pitfalls. You can't make the category axis Continuous Scale or else you'll see all the empty months when we're lumping prior years into Dec. 31. On the other hand, gaps are good visually when there is no data for a given period. So I don't know. But I pictured what you were asking and decided to try it. Buyer beware!