2 Replies Latest reply on Aug 22, 2018 10:33 AM by susheela.hegde

# Average Value Display

Hi All,

I had created a below  thread to ignore Weekend Value and get the count.

I did get this work  but i am stuck with how to display the Over all average value along with certain filter.

I did not want to continue in the same thread and started new one to avoid confusion.

Scenario:

1. I have to calculate  Over all Average value daily, weekly, monthly etc . This is fixed.

2. Display daily, weekly. monthly value when we change the parameters.

3. I want a scorecard which compares Actual Value with the Average (sheet-With both the Values).

I am not able to display actual and Over all Average in the same sheet and have to create two individual sheets.

Is there a way to show Actual vs Average in the same sheet?

• ###### 1. Re: Average Value Display

Hello Chits,

I believe your calclulation for the filter is incorrect and cutting off weeks, months and years.

Try the calculation below instead and see how I ahve adjusted the less than value to NOT be less than or equal but instead less than the next week, month, quarter or year.  Your datetrunc() command makes the math for each of these work because the starting day to add to is always the first day of a week, month, quarter, or year and so the addition values will allow us to ignore any sort of logic to deal with shorter months (February), Leap years, etc..   I hope this helps.

```If [Select Period]='yesterday'
then
IF [Order Date]=(TODAY()-1) THEN TRUE else FALSE end

ELSEIF  [Select Period] = 'day'

then
if [Order Date] = [Date Selection] then TRUE else FALSE end

ELSEIF  [Select Period] = 'week'
THEN
if [Order Date] >= DATETRUNC('week',[Date Selection]) and [Order Date] < DATETRUNC('week',[Date Selection]+7) then
TRUE else FALSE end
ELSEIF  [Select Period] = 'month'
THEN
if [Order Date] >= DATETRUNC('month',[Date Selection]) and [Order Date] < DATETRUNC('month',[Date Selection]+31) then
TRUE else FALSE end

ELSEIF  [Select Period] = 'quarter'
THEN
if [Order Date] >= DATETRUNC('quarter',[Date Selection]) and [Order Date] < DATETRUNC('quarter',[Date Selection]+95) then
TRUE else FALSE end

ELSEIF  [Select Period] = 'year'
THEN
if [Order Date] >= DATETRUNC('year',[Date Selection]) and [Order Date] < DATETRUNC('year',[Date Selection]+367) then
TRUE else FALSE end
END
```

Patrick

• ###### 2. Re: Average Value Display

Patrick,

Thank you very much for the suggestion.

Do you have any thoughts on how to display the averages in scorecard?