Spotfire averaging a field between two dates

0
votes

I've been asked to display the most recent 6 months, and compare the most recent 6 month average to the previous years same 6 month average. I've been able to isolate the dates by using the following formula

[Date] >= dateadd('mm',-6,DateTimeNow()) <-- This is used in a cross table, which averages a "Booked/Available" field

However, I'm not sure how to calculate the average of the previous years 6 months.

I can isolate the dates by using this formula

[Date] <= dateadd('mm',-13,DateTimeNow()) AND [Date] >= dateadd('mm',-18,DateTimeNow())

How would I approach this to figure out what the difference is from the most recent 6 months with the Booked/Available field?

1
sqlspotfire
"display the most recent 6 months" - what does it mean exactly? Do you need to both display "raw" data and compare summaries, or only compare summaries in one visualization? Could you please add some sample data and results to you question? It's difficult to understand now. – Máté Juhász
Thank you for the reply. So I have a cross table setup that displays the percentages of Booked/Available visits. I currently have it displaying only the most recent 6 months, but need to create another field that will show the difference from the previous years 6 months.(e.g. Oct15-Mar16 compared to Oct14-Mar15). – T-Mc

1 Answers

0
votes

Average last six months:

Sum(if([Date] >= dateadd('mm',-6,DateTimeNow()), [ColToAvg],0))/Sum(if([Date] >= dateadd('mm',-6,DateTimeNow()), 1,0))

Average previous six months:

Sum(if([Date] <= dateadd('mm',-13,DateTimeNow()) AND [Date] >= dateadd('mm',-18,DateTimeNow()), [ColToAvg],0))/Sum(if([Date] <= dateadd('mm',-13,DateTimeNow()) AND [Date] >= dateadd('mm',-18,DateTimeNow()), 1,0))

Difference between two averages:

Sum(if([Date] >= dateadd('mm',-6,DateTimeNow()), [ColToAvg],0))/Sum(if([Date] >= dateadd('mm',-6,DateTimeNow()), 1,0)) - Sum(if([Date] <= dateadd('mm',-13,DateTimeNow()) AND [Date] >= dateadd('mm',-18,DateTimeNow()), [ColToAvg],0))/Sum(if([Date] <= dateadd('mm',-13,DateTimeNow()) AND [Date] >= dateadd('mm',-18,DateTimeNow()), 1,0))