9 Replies Latest reply: Dec 9, 2014 2:53 AM by Phil Speight

# Multiple Set analysis

I am looking for a set analysis for a chart.  My criteria is below:

Date within the last 31 days

Shift Source = 'Agency'

I need to sum 'WorkedHours')

Can anyone help, it is driving me insane???

Phil

• ###### Re: Multiple Set analysis

sum({<Date={">=\$(=date(today()-31))"},[Shift Source]={"Agency"}>} WorkedHours)

• ###### Re: Multiple Set analysis

Unfortunately, there are dates which are in the future, so this statement would include those dates.  This solution you gave will show anything greater than 31 days old.

I need it to only be for the last 31 days (not future dates)

Regards

Phil

• ###### Re: Multiple Set analysis

no problem use this

sum({<Date={">=\$(=date(today()-31)) <=\$(=date(today()))"},[Shift Source]={"Agency"}>} WorkedHours)

• ###### Re: Multiple Set analysis

No Afraid Not.

I tried it like this based on my fields:

sum

({<Roster_RosterDate={">=\$(=date(today()-31)) <=\$(=date(today()))"},[Shift_Source]={'NHSP'}>} (Roster_TargetHours/60))

I have also set some variables in the script, I don't know if that is something I could try, I just seem to get lost in quotes, brackets, etc :

vdaysAgo31=Date(Today()-31);
vYesterday=Date(Today()-1);

• ###### Re: Multiple Set analysis

if previous exp works, you can add another condition

sum({<Date={">=\$(=date(today()-31))<=\$(=date(today()-0))"},[Shift Source]={"Agency"}>} WorkedHours)

see attachment

• ###### Re: Multiple Set analysis

It just doesn't seem to want to work.

I think it may be something to do with the date format (or not) which is why I was thinking of using the variables, as they seem to work.

Is it possible to use them in the statement?

Phil

• ###### Re: Multiple Set analysis

Hi Phil,

all mentioned above looks right, but maybe instead of double quoted marks use ' ', but not sure if it works...

Martin

• ###### Re: Re: Multiple Set analysis

yes, you can

I added an expression (last column) to my example

result is the same (with variable or with expression)

another help:if you remove the label of the expresssions (as in my .qvw) you can see the date used in set analysis

• ###### Re: Multiple Set analysis

Ok, a bit starnge, but I created a new field Date(RosterDate) and then applied this to the script.

It worked.