CODEWITHSUNDEEP
bar-chartexpressionspotfirespotfire-analystcrosstable
Jerome
Jerome

Reputation: 1

Spotfire - Issue with "Average Over LastPeriods" when using "Color by"

On a stack Bar Charts I want to visualize the average amount spent per category over the 6 previous months.

My data is structure this way :

Month Category Amount
March 2024 Rent 520
March 2024 Food 230
March 2024 Holiday 326
April 2024 Rent 520
April 2024 Food 196
.... ... ...

With the Bar Chart configured as follow:
Category Axis = [Month]
Colors = [Category]

I have found an expression that is working fine when there exist entries for each category for each month:

Value Axis = Avg([Amount]) OVER (LastPeriods(6,[Axis.X]))

The issue is that when one month is missing a category, for exemple in April there is no spending in the category "Holiday", then the segment "Holiday" for the month of April is not displayed on the bar chart.

I expected it would return a result since I am looking for the average of last 6 months, and there are indeed spending in the category just the month before, so the value can't be 0.

I have tried to get the results via a Cross table as well, with:
Horizontal = [Month]
Vertical = [Category]
Cell values = Avg([Amount]) OVER (LastPeriods(6,[Axis.Columns]))
And I get the same issue. The value is not even computed when there is no entries for a "Month"-"Category" couple, the cells just shows "--".

One workaround could be to add the missing "Month"-"Category" couple with an amount = 0 in my dataset. Issue is this would considerably increase the amount of data (by a factor of 20 I estimate) while I am already at about 1 million entries with 50+ columns in my complete dataset

Upvotes: 0

Views: 117

Answers (1)

Gaia Paolini
Gaia Paolini

Reputation: 1492

If there is no entry to visualize, there is no way to attach a value to it. You can display the value from the normal table via a calculated column, but you understandably do not want to display the whole table. So it looks like you need to change the way you visualize your results.

I suggest using a Waterfall chart:

  • Category axis: Month (as before)
  • Value axis: your 6 month average (as before)
  • Trellis by: Category
  • Add a Label with the running total.

This way you at least see a flat line where the category is missing.

Upvotes: 0

Related Questions