topic Re: Multiple Set analysis in QlikView

Multiple Set analysis

Anonymous — Mon, 08 Dec 2014 15:26:41 GMT

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

Re: Multiple Set analysis

lironbaram — Mon, 08 Dec 2014 15:31:01 GMT

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

Re: Multiple Set analysis

Anonymous — Mon, 08 Dec 2014 15:38:11 GMT

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

lironbaram — Mon, 08 Dec 2014 15:42:53 GMT

no problem use this

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

Re: Multiple Set analysis

maxgro — Mon, 08 Dec 2014 15:52:30 GMT

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

Anonymous — Mon, 08 Dec 2014 15:53:26 GMT

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

Anonymous — Mon, 08 Dec 2014 16:45:06 GMT

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

mato32188 — Mon, 08 Dec 2014 16:56:10 GMT

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

maxgro — Mon, 08 Dec 2014 16:58:35 GMT

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

Anonymous — Tue, 09 Dec 2014 07:53:35 GMT

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