Calculating Average

[QFanatic]

hi guys

PFA

The model contains Sales data per

1. Table_Name(which is actually sales person LOL), 

2. per Hour

3. per Weekday

4. Per PERIOD.

I want to calculate the Average Sales  PER Period , per Weekday, per Hour. So that I can say in May, for Mondays Time slot 4pm till 5 pm, the Average Sale is xxxx amount. 

Then I want to use the above Calculation...and in my cell - (as example) if the Sales for any given Monday between 4 and 5 pm, is MORE than above - color Green Background. If Sales is LESS, then Red background.

I am battling to get the syntax in the Expression right.

Any help appreciated thanks. This is an urgent requirement.

Lorna

[Or]

That is indeed how you would go about using the expression in a variable - I typically only do that if I plan to re-use this expression extensively (in QS, it would just be a Master Item).

As I said, there's no problem with this being a pivot, you just can't pivot by the Weekday dimension (that is, you can't use it at the top like you do Hour). It has to be a left-side dimension like the date.

[QFanatic]

Me again...

Just looking at your statement...'It's actually not as bad as it first looks - we are just passing the possible values in weekday and hour'..why do you need to do this (and use the 'P' in the statement). Does QV not do this intrinsicly?

Thanks again

[Or]

I'm not actually sure how it would work here since we are ignoring the date and using the TOTAL qualifier. Best bet is to try and remove it and see if it works correctly, to be honest. The proof of the pudding is in the eating.

[QFanatic]

hi,

I've 'implemented' your suggestion in my real life model. So far it looks promising - thank you 🙂

I'm baffled that you use 'SUM' in the top part and the exact expression for bottom, but with Count - may I please bother you to explain that to me?

and yes, I've tested - Avg gives exactly the same answer - that also boggles me. I was taught to always use e.g. Sum (top)/count Distinct whatever at the bottom.

Thanks

[Or]

Count just takes the number of values for the specific field (as long as you don't try counting a field which is also a key between two tables, in which case results may be unexpected). You can count any field within the same table and get the same result (unless there's null values anyway).

AVG will work correctly here because you have exactly one value per combination of dimensions. Consider this dataset:

Date - Value

1/1 - 100

2/1 - 200

3/1 - 100

4/1 - 0

The average of these is 100, and the sum(Value) / Count(Value) is also 100, because we have exactly one value per date. If we had this dataset instead:

1/1 - 50

1/1 - 50

2/1 - 200

3/1 - 100

4/1 - 0

Then we could no longer use AVG per day and would have to instead use Sum()/Count().

[QFanatic]

Oh I see. Thank you very much for taking the time to explain to me.