Calculate values based on a rolling date range

[Sam_Thomas]

Hi

I would like to know how I could show a percentage figure on a Gauge chart, based on values with a certain date range - a rolling 30 day period. 

Context - I am trying to show how many Work Orders (maintenance jobs for sites) have been completed over the last 30 days , on a rolling basis. Based on Work Order Number, Raise Date and Close Date. 

Please see attached sample data. 

Ideally, I would like to show this in two different variations. 

First variation - completed jobs/raised jobs %

Second variation - completed jobs/raised job% but for work orders that have been raised and completed within the 30 day period?

Thanks for any suggestions in advance. 

[morgankejerhag]

In the script I would add two columns:

• if([Actual Completion Date]<>'',1,0) as FlagCompleted
• if([Work Order Raised Date]>(today()-30),1,0) as FlagLast30

Then in the gauge you can make a calculation like:

count({<FlagCompleted={1},FlagLast30={1}>} [Work Order])/count({<FlagLast30={1}>} [Work Order])