CODEWITHSUNDEEP
crosstabspotfire
int21h
int21h

Reputation: 324

Spotfire: UniqueSum in CrossTable

Is there a way to use multi-table data in CrossTable together with Sum aggregation? Sample data looks like this: enter image description here

UniqueCount(actid) aggregation works perfect with joined data: enter image description here

But I have to calculate Sum of [bdg] column for each cell, and value for each distinct [actid] should be counted once. When using simply Sum(bdg) values are calculated expected incorrectly: enter image description here

Highlited values should be 1010 and 1110 in this case.

Does anyone have an idea how to get this working as required? Maybe there is some way to use related data tables or implement "UniqueSum" aggregation...

Thanks in advance

Upvotes: 0

Views: 4442

Answers (2)

int21h
int21h

Reputation: 324

Finally found required solution. First, adding couple of calculated columns to have [bdg] value only for single row per unique [actid]:

rank: Rank(RowId(),"desc",[actid])

bdg_unq: If([rank]=1,[bdg],null)

Then we can use following expression as aggregation in cross table:

Sum([bdg_unq]) over (Intersect(All([Axis.Rows]),[Axis.Columns],[actid]))

enter image description here

Upvotes: 1

flux
flux

Reputation: 1648

Right click on the Sum(bdg) property and choose Custom Expression...

Try this:

Sum([bdg]) OVER ([actid])

This should slice the data over actid. Otherwise play around in there till you get what you want. Here's more info on the OVER function:

http://stn.spotfire.com/spotfire_client_help/ncfe/ncfe_over_functions.htm

Upvotes: 2

Related Questions