Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted
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
Highlighted
Partner
Partner

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

Highlighted
Creator
Creator

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

Highlighted
Partner
Partner

no problem use this

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

Highlighted
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

Highlighted
Creator
Creator

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

Highlighted
Creator
Creator

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

Highlighted
Creator III
Creator III

Hi Phil,

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

Martin

Highlighted
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

Highlighted
Creator
Creator

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