Many-To-Many Date Issue

[#1]

My data shows sales data with two date fields: "ServiceDate" and "BatchDate" (the date posted to the general ledger).  My master calendar is built on ServiceDate, and that works for all of the objects in my project except one.  I need to show that one chart based on BatchDate.

See the attached data example.  The real problem is that there is a many-to-many relationship here.  Revenue for any ServiceDate may be posted on multiple BatchDates, and any BatchDate may include data from multiple ServiceDates.  As a result, I can't use a simple statement in my script like:

if(BatchDate=ServiceDate).

I want the end user to select a ServiceDate from the calendar, and have this one chart display sales data based on BatchDate, including all ServiceDates posted to the GL on that day.

Does anyone have an idea on how to accomplish this, or am I out of luck?

[swuehl]

What do you mean with

"My master calendar is built on ServiceDate" ?

I would assume that your master calendar is not build on any other date fields, but entirely cover a certain timespan. Then I would link the master calendar as well to ServiceDate as to BatchDate.

Probably I do misunderstand something, could you post the relevant table view of your data model?

Regards,

Stefan

[#3]

Stefan:

Please see the attached file (export.png) for my table view.  My Master Calendar is built off of the KeyDate, which is created in the script in the BarChargeTransactions table as:

Date(ServiceDate) AS KeyDate

I give the users a group of list boxes at the top of each tab in the app, where they select the time period they wish to view.  (See Calendar List Boxes.gif)  Those list boxes are created in the Master Calendar script, and are based on KeyDate .  So, when users make their selections in those list boxes, all of the objects show data for the selected KeyDate(s).

All of my objects in this app use KeyDate, except the one that shows daily revenue.  That must show revenue by day, using BatchDate.  I don't want to ask my users to clear their date selections, then select the BatchDate(s) they want to see.  So, what I want to do is create an expression that looks at the KeyDate(s) selected in the list boxes, and shows me total charges based on the BatchDate.

As I mentioned earlier, I can't use an IF statement in the script, like "IF(BatchDate=KeyDate, Sum(ChargeAmount))".  That would omit charges with KeyDates that are not equal to the BatchDate.  I can't think of a way to do it using set analysis either.

I'm starting to believe that the many-to-many relationship between KeyDate and BatchDate means this is not possible.

I'm very new to this, so any ideas are welcome.

[swuehl]

Sam,

I think I have now an idea of what you want to achieve.

I tried to condense of what I think makes up the problem into a small qvw using the excel data you provided above.

Then I tried to show the total charges based on the BatchDate (w looking at KeyDate selection) and ServiceDate Amounts.

I ended up with a short set expression. Please find my sample attached.

I didnt't managed until now to incoporate both tables into one using a common time dimension, but as I understand, this is not your primary requirement.

So I stopped for today, If you have any questions, don't hesitate to get back to me.

Regards,

Stefan

[#5]

Stefan:

Thank you for the example.  That's exactly what I'm trying to show.  Unfortunately, when I apply your expression in your "Charges Based on Batch Date" table in my model, it returns zero.  I verified that all fields are correctly named, and had to change one field name.

I'll try to create and upload a smaller QVW with our data.  I have to be very careful, though.  We are a hospital, and are subject to Federal privacy laws.

If you have any other ideas, I'd be glad to hear them.

[swuehl]

Sam:

just an idea, you probably need to clear also the other key fields in set expression or what ever user may select:

= sum( {<BatchDate = KeyDate, KeyDate= ,BillingID= ,KeyBudget= >} Amount)

Stefan