CODEWITHSUNDEEP
spotfire
donut_person
donut_person

Reputation: 25

How to aggregate data in table by month in spotfire

So my original table is like this:

enter image description here

And I want to compress the table which only shows that factoryID and the month, and the max output like this:

enter image description here

How do I do this with spotfire in table visualization?

Upvotes: 1

Views: 1631

Answers (2)

Sudhendu
Sudhendu

Reputation: 380

  1. Create two caculated columns.

c_month, c_max

for c_month, use the expression: Concatenate(Month([date]),"-",Year([date]))

for c_max, use the expression: Max([max]) OVER ([factory id])

  1. Add third column called c_rank expression:

If(Rank(RowId(),"asc",[factory id])=Min(Rank(RowId(),"asc",[factory id])),True)

//this will create a new calculated column which will give true only for every unique value of factory id.

  1. Add a data table to your visualization

  2. Add data limiter

Right click and under data --> Limit Data using expression, enter [c_rank] = true

  1. Create Table with limted columns

Only add factory id, c_month and c_max to your column list.

enter image description here

Upvotes: 3

Mark P.
Mark P.

Reputation: 1827

To go from the top table to the bottom table: enter image description here

Do these steps:

  1. Create a new calculated column off of your date for mmm-yy: Concatenate(Month([Date]),"-",right(String(Year([Date])),2))
  2. Add a pivot transformation with the following parameters: enter image description here

I created it as a new table but you do not have to.

Upvotes: 4

Related Questions