Date Filter not filtering on calculated columns

[Manoj Chaurasia]

Hello Spotfire Experts,

 

I am trying to make a simple report(as its the my first spotfire report) but somehow not able to achive the expected results.

 

Please refer the attached screenshot: [first.jpg]

 

here, StoreNo, CustomerGrade and karat are used as distinct filelds and rest of the columns are calculated based on the multiple records available in the db table. like, TotalCost(in the RED box) is the sum of all the records in DB having StorNo: 2234, CustomerGrade: C, Karat: 10K and as the other RED box, total no of records partcipated in the calculation are 6.

here TotalCost is using this calculation: Sum([Cost]) OVER ([storeNo],[Karat],[CustomerGrade])

 

Now, if I change the reduce the no of days from the TransactionDate (this is one of my information link field but I am not showing it on report but it need to be used as filter), then though the no of records are reduced like and only those which fulfill the date range are shown in the visualization.

 

but TotalCost calculated column still shows the same amount as long as there is a single record satisfying the filter range.

that means filter and calculated columns work independently, is this correct

 

Please refer the attached screenshot: [second.jpg]

Is there any way we can calculated only those rows which satisfy the filter range

 

Your response is much appreciated.

 

Thanks,

Varun

[Manoj Chaurasia]

You could also try a summry table categorized by your 3 distinct fields and then use marking to filter down the summary table by dates selected in the main table.

[Manoj Chaurasia]

Varun,

 

Calculated values will use all data and don't honor filters from your filtering schemes. In order to work around this, I've had to hard-code filters into the calculated column equation with if-statements. The major downside to this is that the formula is not dynamic. If you are intending to plot this column, you can use a custom expression, which will honor your filtering scheme.

[Manoj Chaurasia]

Hi Varun! Just wondering if you found a solution to this problem. I am having the same problem. Could you kindly let us know how to work around this please

[Michael Kalada 2]

There is a wiki that shows how to make calculated columns in a table respond to filters. Its a python script, there are other posts that show how to execute a python script on filter changes

 

https://community.spotfire.com/wiki/calculated-columns-table-respond-filters