Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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
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
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
)
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)
Hi Joe,
What you've pasted is exactly what I have in the chart expression and nothing is displayed...
Cheers
Mark
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)
Hi Joe,
Yes, I just realised, (a little slow today).
thanks for all your help!
Mark
no worries, glad the extra pair of eyes on it helped
Joe