Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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???
Thanks in advance
Phil
if previous exp works, you can add another condition
sum({<Date={">=$(=date(today()-31))<=$(=date(today()-0))"},[Shift Source]={"Agency"}>} WorkedHours)
see attachment
sum({<Date={">=$(=date(today()-31))"},[Shift Source]={"Agency"}>} WorkedHours)
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
no problem use this
sum({<Date={">=$(=date(today()-31)) <=$(=date(today()))"},[Shift Source]={"Agency"}>} WorkedHours)
if previous exp works, you can add another condition
sum({<Date={">=$(=date(today()-31))<=$(=date(today()-0))"},[Shift Source]={"Agency"}>} WorkedHours)
see attachment
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);
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
Hi Phil,
all mentioned above looks right, but maybe instead of double quoted marks use ' ', but not sure if it works...
Martin
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
Ok, a bit starnge, but I created a new field Date(RosterDate) and then applied this to the script.
It worked.
Thanks all for your help