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]

I'm having a hard time understanding what your expected outcome is. Your pivot has Date and Hour as dimensions, which means that for each individual cell, the Average is always going to be identical to the Sales, since it only reflects a single value of Hour.

It would be helpful if you could provide an example of what the expected value would be at least for the first few cells in the pivot.

[QFanatic]

Let's say for 20 may, which was a Wednesday.. I compare Wednesdays sales let's use between 1 pm and 2 pm as a reference..) to the average sales of all the Wednesdays in that period, in that time slot.

I suspect that I'd need some kind of aggregation to achieve that.. Something like.. Avg(aggr( Sum(sales) / count Wednesdays in the Month), req_hour.

Hope you inderstand better now

[QFanatic]

Hello

Please find attached a really simple excel file, as well as my model.

So from the data you can see there's data for 3 Friday's and 1 Saturday.....for the 16:00 hour slot.

I then would need to take the daily volume(Per Hour) and divide that by the Amount of Days (as mentioned above) that HAS a 16:00 time slot..so the total volume for the Fridays 16:00 hour slot is 552.5/3 = 184.17

The columns that the user requires to see is 1) Date, Hour and Volume. They DONT want to see WEEKDAY...

It seems so simple...but its not. @sunny_talwar  any chance you can assist? 

Much appreciated

[Or]

It doesn't sound simple at all, since you're mixing three different aspects here:

* Ignore the date

* Respect the hour

* Respect the WKDAY even though it's not in the chart at all

The former two are easy enough, but combined with the third one, it gets very tricky. If WKDAY is in the chart, it becomes much easier to solve, but your requirement is not to have that, and unfortunately I haven't been able to get it to work without including WKDAY as a dimension.

[QFanatic]

Thanks for your trouble.

Would it help to include weekday as a Dimension? if that will solve the situation, the users will just have to understand

[Or · Accepted solution]

If WKDAY is a dimension (after date and before hour, not pivoted of course), then this should work:

=Sum({$<PERIOD={$(vMaxMonth)},REQ_DATE=, WKDAY = p(WKDAY), REQ_HOUR=p(REQ_HOUR) >}total <WKDAY,REQ_HOUR> HOUR_VOLUME)
/
count({$<PERIOD={$(vMaxMonth)},REQ_DATE=, WKDAY = p(WKDAY), REQ_HOUR=p(REQ_HOUR) >}total <WKDAY,REQ_HOUR> HOUR_VOLUME)

In theory, I was expecting this to work even without the WKDAY dimension, but the fact is that it didn't, and I'm not entirely sure why - I don't typically write this sort of set analysis so it may well be something obvious I'm just not seeing.

Perhaps someone else with more experience in this particular regard can clean this up so it works without the extra dimension, but I wasn't able to.

[QFanatic]

Hello Or,

Wow that is a hectic statement. Let me sit and unpack it, then I will get back to you. Just saw that you said this shouldnt be pivoted - any specific reason?

Now this complex statement, I will need to incorporate in a ColorWiz statement...That will enable users to see 

1) what the calculated average is (per Wkday per hour)  - your statement

2) Compare the actual Volume in the ColorWiz statement and background using this.

Much appreciated- I will revert

[Or]

It's actually not as bad as it first looks - we are just passing the possible values in weekday and hour, and applying a TOTAL to these fields as well (so that the result ignores the weekday dimension). Otherwise it's the same formula except one is a count and one is a sum - I think you could actually just use one formula with AVG() but I wasn't sure if your data points are singular per hour/day combination or plural.

Insofar as the pivot, you shouldn't pivot the weekday because it makes no sense to pivot the weekday. Each date is only associated with a single weekday, whereas the hours (which are pivoted) are associated with many weekdays, so the weekday shouldn't be pivoted, it should be part of the left-side dimensions, same as the date.

You can compare your existing sum formula and the new average formula in the color property, I believe, so it's just a matter of writing out the specific conditions you want. If you plan to be using this across many objects/dimensions, you might want to consider writing this entire expression into a variable and then using that variable in its lieu across the different objects and expressions. This will let you control the entire thing from one place (the variable), but the price is less readable expressions.

[QFanatic]

Thank you for taking the time to explain.

So if I write the entire calculation into a variable, I'd just create a Variable called e.g. AvgPerHour and make that equal to the expression (including the leading '=').

Then when I use that, in my Expression would I just say =$(AvgPerHour) or use it without the leading '='.

I'm never quite sure about that.

So my users are used to seeing a Pivot,even if it doesnt make sense to you and me, I will keep it that way and not rock the boat:)-