topic Re: Set Analysis help required in QlikView

Set Analysis help required

RedSky001 — Mon, 07 Nov 2011 12:05:00 GMT

See the attached qvw and the following diagram for more detail.

The minimum date (vMinDate) for all data is 01-Oct-2010.
The user selects the date range (Nov '11).

I want to sum QTY (imp) delivered from the minimum date (01-Oct-2010) to the maximum user selected date (31-Nov-11)
I've done this with this statement.

sum({$<Date={">=$(vMinDate)<=$(=max(Date))"}>} Imp)

But I also want to exclude expired Ad's, where the an end date is less than the minimum user selected date (01-Nov-11).
I don't know how to do this with Set Analysis, please help.

Regards
Mark

Re: Set Analysis help required

Mon, 07 Nov 2011 12:24:55 GMT

Hi Mark,

If I understand correctly I think the below should do the trick for you,

Sum({$<Date={">=$(vMinDate)<=$(=max(Date))"}, [AD End Date]-={"<=$(=min(Date))"}>} Imp)

hope that helps

thanks

Joe

edit:

sorry just noticed you wanted the min date not the max, have changed now for that so will bring back 1,2,3 and 6

Set Analysis help required

RedSky001 — Mon, 07 Nov 2011 12:34:49 GMT

Hi Joe,

Thanks but that didn't work.

Ad Id 5 has a start date of 10-Aug-11 and end date of 10-Oct-11

I want to exclude it because the end date is less than the user's selected start date.

In the "With Set Analysis" table in the attached QVW, I need to exclude Ad Id 5, everything else there is fine.

(Hopefully that makes more sense)

Mark

Set Analysis help required

RedSky001 — Mon, 07 Nov 2011 12:42:55 GMT

I understand your logic (I actually had got this far but didn't want to cloud the orginal post with something I thought was wrong). However with this expression the chart just appears blank..Here's the exact syntax so you can see for yourself:

sum

({$<
Date={">=$(vMinDate)<=$(=max(Date))"}
, [Ad End Date]-={"<=$(=min(Date))"}>}
>
} Imp
)

Re: Set Analysis help required

Mon, 07 Nov 2011 12:54:52 GMT

Hi Mark,

that should work fine, if you put all yours on one line you will see straight away what you have done, just need to remove the extra there so the set is closed properly and it will work for you (correctly remove 4 & 5 from the list for that date range)

thanks

Joe

sum({$<Date={">=$(vMinDate)<=$(=max(Date))"}, [Ad End Date]-={"<=$(=min(Date))"}>} >} Imp)

Re: Set Analysis help required

RedSky001 — Mon, 07 Nov 2011 13:01:57 GMT

Hi Joe,

What you've pasted is exactly what I have in the chart expression and nothing is displayed...

Cheers

Mark

Re: Set Analysis help required

Mon, 07 Nov 2011 13:06:28 GMT

Hi Mark,

sorry was just trying to show you the error there that's all, you just need to remove the extra >} and it will work fine.

thanks

Joe

Yours:

sum({$<Date={">=$(vMinDate)<=$(=max(Date))"}, [Ad End Date]-={"<=$(=min(Date))"}>} >} Imp)

Mine:

sum({$<Date={">=$(vMinDate)<=$(=max(Date))"}, [Ad End Date]-={"<=$(=min(Date))"}>} Imp)

Re: Set Analysis help required

RedSky001 — Mon, 07 Nov 2011 13:07:35 GMT

Hi Joe,

Yes, I just realised, (a little slow today).

thanks for all your help!

Mark

Re: Set Analysis help required

Mon, 07 Nov 2011 13:09:17 GMT

no worries, glad the extra pair of eyes on it helped

Joe