Counting data and expressions

[#1]

Hi all,

I've just generated this table:

The "Number of Orders" field is being displayed by counting the number of orders after they've been filtered by Month and by Advice Status. What I want to do thuogh, is display these numbers as a percentage by week, or by whichever "Time Cycle" is being used. For example,the week of "9 Nov 2008" has 26 orders in total, so for appropriate orders I want to display 34.7% (9/26 x 100).

Seems simple enough but I can't figure out how to get the total number of orders in a Time Cycle. The way i'm calculating number of orders, is with the expression: =count(distinct [order id]), which after the table does all the filtering, gives me a number by Time Cycle by Advice Status. What I can't figure out, is how to incorporate the total number of orders in a time cycle so that I can make the division and get a percentage instead of a number.

Any ideas?

[#2 · Accepted solution]

You should be able to use:

Sum(Orders) / Sum(total <Week> Orders)

The total <Dim> part of the expression essentially sums by that dimension.

Sorry, I just went back to read that second paragraph and I'm not certain this will work with how you're calculating the Orders. I thought it was a field. I think for your situation you should try:

Count(distinct [order id]) / Count(distinct total <"Time Cycle"> [order id])

[#3]

thanks again NMiller!