Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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???

Thanks in advance

Phil

1 Solution

Accepted Solutions
maxgro
MVP
MVP


if previous exp works, you can add another condition

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


see attachment


View solution in original post

9 Replies
lironbaram
Partner - Master III
Partner - Master III

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

Anonymous
Not applicable
Author

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

lironbaram
Partner - Master III
Partner - Master III

no problem use this

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

maxgro
MVP
MVP


if previous exp works, you can add another condition

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


see attachment


Anonymous
Not applicable
Author

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);

Anonymous
Not applicable
Author

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

mato32188
Specialist
Specialist

Hi Phil,

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

Martin

ECG line chart is the most important visualization in your life.
maxgro
MVP
MVP

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

1.png

Anonymous
Not applicable
Author

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