Set Analysis help required

[RedSky001]

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

Untitled by AnAngryBunny, on Flickr

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

[#2]

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

[RedSky001]

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

[RedSky001]

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
)

[#5 · Accepted solution]

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)

[RedSky001]

Hi Joe,

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

Cheers

Mark

[#7]

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)

[RedSky001]

Hi Joe,

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

thanks for all your help!

Mark

[#9]

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

Joe