Using a dimension in an expression that is not in chart

[#1]

Hi All,

I have a business requirement that says that I need to find an average of loads by hour of day.  The user wants to look at this for each month using the Hour of Day as the Dimension and the following expression:

# of Loads received in Hour of Day / # of Days in month with receipts

For example:  in total in September at 6:00 am, we received 355 loads / 26 days out of September that we received trucks.

As there are possible selections that can be made such as category, or product, I don't believe that I can do this in the script, but how can I calculate the # of Days in month with receipts when that particular dimension is not in the chart.

I thought I could use AGGR, but I have read that it is unsafe to use AGGR in charts when the AGGR dimensions are not more granular than the chart dimensions.

Any help is greatly appreciated!

[Anonymous]

Should work fine using Aggr() in the front end.

[swuehl · Accepted solution]

You probably don't need aggr() for that.

If your dimension is Hour, then count the loads maybe like

=Count(Loads)

If you count the distinct dates of the selected month,

= Count(Distinct Date)

this would only return distinct dates for the current dimension line (e.g. 06:00), while you - as I understood - want the number of dates where you received trucks regardless of the hour.

=Count(TOTAL Distinct Date)

should return the distinct dates of the selected month ignoring the dimension.

If you want more help, you would need to post some more details about your application and data model, best by posting a small sample QVW.

[#4]

This worked perfectly...thank you!