Set Modifier Question

[spikenaylor1]

My current expression for a Gauge Chart is

=num(sum({<[Production Area]={'Upstream'}>}RFT)/Count({<[Production Area]={'Upstream'}>}RFT),'#.##%')

I want to also filter to the current month, but keep getting errors

I want it to end up something like this

=num(sum({<[Production Area]={'Upstream'}, [Date Document Final approved] >= MonthStart(now()), [Date Document Final approved] <= MonthEnd(now())>}RFT)/Count({<[Production Area]={'Upstream'}, [Date Document Final approved] >= MonthStart(now()), [Date Document Final approved] <= MonthEnd(now())>}RFT),'#.##%')

I understand the statements need wrapping up with {} but cant get it to work, I will keep trying, but could do with some pointers or help.

Could anyone please help me out.

Much appreciated.

Spikenaylor

[Anonymous]

Hi

we will give another try.....

=Count( $ {<[Production Area]={'Upstream'}, [Date Document Final approved] = { ">= $(= Date(MIn([Date Document Final approved]), 'MM/DD/YYYY')) <= $(= Date(Max([Date Document Final approved]), 'MM/DD/YYYY')) "}>}RFT)

[spikenaylor1]

Nope, that just throws up an error.

I have tried another route I found on the internet just to try and prove concept.

in my load script I have

set vCurrentMonthStart = Date(MonthStart(now()));

in a textbox with this formula

=$(vCurrentMonthStart)

01/08/2016 is shown - this is the monthstart

in another text box, i have the following formula

=Count({< [Date Document Final approved] = {"$(=vCurrentMonthStart)"}>}RFT)

The resulting value is a count of all records in the database, in this case 1100 when it should be 5, it still does not only count records from 01/08/2016

the [Date Document Final approved] field when shown in a table object is listed in the following format

dd/mm/yyyy hh:mm:ss

I have tried using a

Date([Date Document Final Approved]) as DateDocumentFinalApproved;

in the load script and this shows in a format of

dd/mm/yyyy in the same table object

I have tried both ways but still cannot get the set modifier expression to only count records from a certain date.

if necessary I can post an example record set if that can be any help.

many thanks for all your ongoing help and advice