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

try this !! But I doubt on the Date formats .........

=num(sum({<[Production Area]={“Upstream”}, [Date Document Final approved] ={“>=$(=Monthstart(now())<=$(=MonthEnd(now())”}>}RFT) 

/ 

count({<[Production Area]={“Upstream”}, [Date Document Final approved] ={“>=$(=Monthstart(now())<=$(=MonthEnd(now())”}>}RFT) ,’#.##%’)

[Anonymous]

Hi,

Try  it.

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

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

Regards

[Anonymous]

Hi

Try this as well ...with date formats

=num(sum({<[Production Area]={“Upstream”}, [Date Document Final approved] ={“>=$(=Date(Monthstart(now()),’MM/DD/YYYY’)<=$(=Date(MonthEnd(now()),’MM/DD/YYYY’)”}>}RFT) 

/ 

count({<[Production Area]={“Upstream”}, [Date Document Final approved] ={“>=$(=Date(Monthstart(now()),’MM/DD/YYYY’)<=$(=Date(MonthEnd(now()),’MM/DD/YYYY’)”}>}RFT) ,’#.##%’)

[vishsaggi]

Try this:

=num(sum({<[Production Area]={'Upstream'}, [Date Document Final approved] = { ">= $(= Date(MonthStart(now()), 'MM/DD/YYYY')) <= $(= Date(MonthEnd(now()), 'MM/DD/YYYY')) "} >}RFT)

/

     Count({<[Production Area]={'Upstream'}, [Date Document Final approved] = { ">= $(= Date(MonthStart(now()), 'MM/DD/YYYY')) <= $(= Date(MonthEnd(now()), 'MM/DD/YYYY')) "}>}RFT),'#.##%')

[spikenaylor1]

Thanks for the help but the formulas are counting all the data not filtering down to the current month.

I have broken it down into two text boxes to see the formula results.

both give results for all data within Upstream.

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

produces a count of 338 which is the total count for all the data within Upstream.

on the source data I filter down to the current month and my result should be 5

the formulas date filtering doesn't seem to be working for me.

[Anonymous]

Hi

count({<[Production Area]={“Upstream”}, [Date Document Final approved] ={“>=$(=Date(Monthstart(now()),’MM/DD/YYYY’)<=$(=Date(MonthEnd(now()),’MM/DD/YYYY’)”}>}RFT),’#.##%’)

Here the filter only applies to the field  :[Date Document Final approved] and [Production Area]

Not on other fields ,.....

How do you select this Month (Is this Another field) ?

on the source data I filter down to the current month and my result should be 5

the formulas date filtering doesn't seem to be working for me.

[spikenaylor1]

I was referring to checking the source data within the database to confirm results.

So the formula returns the number 343 which is the count of all records for all dates[Date Document Final Approved] in Upstream [Production Area].

it should be returning the number 5 which is the count of all records for the dates [Date Document Final Approved] that are >= MonthStart(Now()) and <=  MonthEnd(Now()) in Upstream

So the formula seems to be filtering for the [Production Area] but not for the [Date Document Final Approved]

Sorry for any confusion.

[Anonymous]

Hi

So far i know the above expression will give only for the month AUGUST  and  [Production Area] ='upstream'

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

Or you have to mention the other fields for Ignoring them .........

=AGGR(Count({<[Production Area]={'Upstream'}, [Date Document Final approved] = { ">= $(= Date(MonthStart(now()), 'MM/DD/YYYY')) <= $(= Date(MonthEnd(now()), 'MM/DD/YYYY')) ",X=,Y=,Z=}>}RFT),[Production Area])

[spikenaylor1]

Yes I agree

The formula should show results for within the current month of August and within upstream but it shows the result for all records within upstream ignoring the month filtering

Could it be something about the [date document final approved] format

Does that field need to be formatted correctly in the load script

Currently it shows in a table as did/mm/yyyy 00:00:00

I have tried changing it in the load using date function to show in a table as did/mm/yyy

But the formula still ignores the month filter

Regards