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?
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.
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.
Many-to-Many.qvw 138.0 K
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.