Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

philmywallet
New Contributor III

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

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


9 Replies
lironbaram
Honored Contributor II

Re: Multiple Set analysis

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

philmywallet
New Contributor III

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

lironbaram
Honored Contributor II

Re: Multiple Set analysis

no problem use this

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

MVP
MVP

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


philmywallet
New Contributor III

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

philmywallet
New Contributor III

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

mato32188
Contributor III

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

MVP
MVP

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

1.png

philmywallet
New Contributor III

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.

Thanks all for your help

Community Browser