Can i use analysis to build a load balance graph?

[#1]

I work in a trucking enviroment and am currently trying to build a Graph on QV that will show the number of loads picking up AND the number of loads that are delivering on that day. Each 'TripNo' has a field 'PickUpDate' and 'DeliveryDate'.

I can build graphs of each individually using a graph with a dimension of either 'PickUpDate' and Expression or 'Count (TripNo)' OR 'DeliveryDate' and Expression or 'Count (TripNo)'. This produces seperate graphs but i would like a combines graph with 'PickUpDate' As the dimension and a count of both Pickups on that day and Deliveries on that 'PickUpDate'

If i use a function where PickUpDate=DeliveryDate then i only end up counting same day loads. I need to show all loads that deliver on that day even if thay are not selected that is why i am thinking i need to use set analysis but i am not sure how it works.

[prieper]

Hi,

understand that you like to have a kind of stacked bar-chart over pickup-date?

Thus you should create two expressions, like

Count(If(PickUpDate = DeliveryDate, TripNo))

and

Count(If(PickUpDate <> DeliveryDate, TripNo))

HTH

Peter

[#3]

I made a little progress since i first asked this question. This graph above is closer to what i am looking for:

I added a TripCounter feild so i can sum instead of count.

This graph has "PickUpApptDate" as a Dimension. The Expression Blue line = Sum(TripCounter)

The Red is

Sum

( {<DeliveryApptDate = {'5/5/2009'}>} TripCounter)

It is currently showing count of pick ups that deliver on 5/5/09. I want it to instead show all of these 800+ loads to all apear right under the Pickup total for 5/5/09 (and every other date). The goal is for load trackers to see that there is 300 less trucks delivering into that city then are picking up for each day.

It is a tough to explain but i hope you can get it.

[johnw]

One approach would be to add a disconnected Date field as your dimension, then check PickUpApptDate=Date for the one expression, and DeliveryApptDate=Date as the other. That's inefficient from a processing standpoint, though.

You could add a table with an ID, Date Type, and Date. The ID would map to your main table with the TripCounter field on it. The Date Type would be either Pick Up or Delivery. Again, you would have Date as your dimension, but this time, you could either use Date Type as another dimension, or use Date Type in your expressions, either with an if or with set analysis. Either should be much more efficient than the other proposal with a disconnected Date field.

There might be a way without adding a new field or table, but I'm not thinking of it. The problem with set analysis is that I believe you can't refer to the current value of a dimension in a table, but only to ALL values of that dimension. Perhaps I'm wrong, though, and have merely not come up with the right syntax.

[rwunderlich]

While not a Set Analysis solution, I would build a new table like this and then use ActivityDate as the dimension. Example attached.

Activity:
LOAD TripId,
 PickupDate as ActivityDate,
 1 as Pickup
RESIDENT Traffic
;
CONCATENATE LOAD TripId,
 DeliveryDate as ActivityDate,
 1 as Delivery
RESIDENT Traffic
WHERE len(DeliveryDate) > 0
;

-Rob