Date range Set Analysis question

[alexis]

Consider the diagram below:

• The Calendar is linked with Package via PackageDate
• Package is linked to Activity via PackageID

The users selects a period on the calendar (say a Year and a Month)

I have defined 2 variables:

vMinPackageDate = Min(PackageDate)

vMaxPackageDate = Max(PackageDate)

These correctly store the limits of the range selected

My requirement is to SUM(ActivityAmount) for the date range defined by vMinPackageDate and vMaxPackageDate

= sum({$<ActivityDate={">=$(Date(vMinPackageDate))<=$(Date(vMaxPackageDate))"}>} ActivityAmount)

I am getting results that are NEARLY right; the problem is that when the user selects the period, this affects the range of Package records which in turn affects the range of selected records in Activity.

I want the SUM(ActivityAmount) for the date range vMinPackageDate ... vMaxPackageDate independent of Package...

I have tried various variants like:

= sum({$<PackageDate=, ActivityDate={">=$(Date(vMinPackageDate))<=$(Date(vMaxPackageDate))"}>} ActivityAmount)

but nothing seems to work. Any pointers?

Thanks in advance

Alexis

[MK_QSL]

Your sample data would be highly preferable to give you answer.

[Gysbert_Wassenaar]

Try defining your variables as =Min({1}PackageDate) and  =Max({1}PackageDate)

[alexis]

Hi Gysbert

Thanks for replying.

If I define my variables like that they will always contain the same values irrespective of user selection.

I want the user to use the calendar to select a data range and that's it! Using that date range I then want to select my Activity records independently of the Package records above them if that makes sense!

Alexis

[#5]

To not consider the Package Selections try:

= sum({$<Package=, ActivityDate={">=$(Date(vMinPackageDate))<=$(Date(vMaxPackageDate))"}>} ActivityAmount)

Sample data and desired result would be very helpful.

[alexis]

Thanks Manish - what you see here is a simplistic view of a fairly complex application so to create data would be quite a task (I think 🙂 )

[alexis]

Hi Patrick

"Package" is a table name not a field so not sure how "Package=," will work.

Package table IS joined to the Activity table and that is the very reason that my results are not what I want.

I basically want to return the records that are defined by the date range in the calendar (as stated before) independent of Package...

[alexis]

You would have thought that:

= sum({1<ActivityDate={">=$(Date(vMinPackageDate))<=$(Date(vMaxPackageDate))"}>} ActivityAmount)

(i.e. replacing the "$" with a "1" to consider the entire set but this one always returns that entire record set ignoring my date range selection...

[Gysbert_Wassenaar]

I'm not following you. You want to select a date range in the PackageDate field. That selection will determine the min and max values in your variables..... which are values you say are the wrong values since they relate to a limited set of Packages (the possible values resulting from your selecting in PackageDate). I don't understand why or what you feel the correct values should be.

Perhaps you want to create an additional calendar for the ActivityDate and select the date range in that field.

[#10]

Looks like your easiest way would be to create a Summary table. (No need for variables!) Kindly see attached sample.

or a simpler Summarytable (depends on the your granularity) would be-

SummaryTable:

load

  ActivityDate as Calendar_Date,

  sum(ActivityAmount) as _ActivityAmount,

  'ActivityAmount' as SummaryType

resident Activity

group by ActivityDate

;

You can use this Summary Table for other Summaries, just create a different 'SummaryType'. HTH!