Last Year Average

building reports desktopintelligence
1

Hi All,

Below formula gives the YTD (Jan2010 to Till date) average for CurrentRankVar

=Average(If< Received Date>>RelativeDate(CurrentDate() ,-DayNumberOfYear(CurrentDate()))  Then <CurrentRankVar> Where( <PSL NA Core>="F1"))

I need code where I should get the average for LastYear (2009) provided ReceivedDate variable has the dates. Some RelativeDate function logic is required but not sure how to make it. Any help to achive this will be greatly appreciated.
Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

2

John,

How about CurrentDate() -365 ?

Thanks…

nickie :us: (BOB member since 2010-02-16)

3

Thanks Nickie. The formula is as below and works,

=Average(If<Received Date>>RelativeDate(CurrentDate() ,-DayNumberOfYear(CurrentDate()-365))  Then <CurrentRankVar> Where( <PSL NA Core>="F1"))

So this will give the average from 2009-Jan to 2009-Dec correct? because I will not able to verify the numbers.

Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

4 
=Average(If< Received Date>>RelativeDate(CurrentDate() ,-DayNumberOfYear(CurrentDate()))  Then <CurrentRankVar> Where( <PSL NA Core>="F1"))

John,

I would think you were pretty close with the Use of a Where clause. Can you create a “Flag” Variable that is based on identifying the Previous Year? If so, then you could use something like:

=Average( If <PrevFlag>="Y" then <RankVar> )

digpen :us: (BOB member since 2002-08-15)

5

Hi Digpen,

I have a currentyear Flag like this,

=If(Year(<Received Date>)=Year(CurrentDate())) Then "Y"

And, I created a Prevyear flag as below,


=If(Year(<Received Date>)=Previous(Year(CurrentDate()))) Then "Y"

But when I tested by just dragging to a column it throws #Multivalue error. Any problem in that formula?
Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

6

Hi Digpen,

I think I got it, Below is my Formula,

PrevyearFlag=If(Year(<Received Date>)=Year(CurrentDate()-365)) Then "Y" 
AveragePrevyear=Average( If <PrevyearFlag>="Y" Then <CurrentRankVar> Where (<PSL NA Core> ="F1"))
AverageYTD=Average(If<Received Date>>RelativeDate(CurrentDate() ,-DayNumberOfYear(CurrentDate()))  Then <CurrentRankVar> Where( <PSL NA Core>="F1"))

Any bugs in the formula?

Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

7

The only thing I can think of offhand is that the -365 for determining previous year may not work for a leapyear (if run on the last day of the year).

For something like that, I usually take the Year of the CurrentDate and subtract one. That’s normally enough of a work around (in the very very unlikely case that someone runs the report on New Years eve on a leap year).

Otherwise, without seeing the data, the code looks fine to me.

digpen :us: (BOB member since 2002-08-15)

8

Thanks Digpen. I appreciate your help!

You mean replacing 365 by 1? Isnt it correct?

Also, I am little suspicious about the YTD formula,

AverageYTD=Average(If<Received Date>>RelativeDate(CurrentDate() ,-DayNumberOfYear(CurrentDate()))  Then <CurrentRankVar> Where( <PSL NA Core>="F1"))

Can you please check if this makes sense?

Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

9

I mean instead of:

Year(CurrentDate()-365))

try

Year(CurrentDate())-1

digpen :us: (BOB member since 2002-08-15)

10

Thanks Digpen. Yeah, that makes sense and worked.
How about this below YTD? Does this makes sense?

AverageYTD=Average(If<Received Date>>RelativeDate(CurrentDate() ,-DayNumberOfYear(CurrentDate()))  Then <CurrentRankVar> Where( <PSL NA Core>="F1"))

Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

11

I look at a Previous/Current year flag as:

 = if Year( <DateField> ) = Year( CurrentDate() ) then "Curr" else if Year( <DateField> ) = Year( CurrentDate() )-1 then "Prev"

Then my formulas can be defined like:

=Average( If <PrevFlag>= "Curr" then <FieldtoCalcAvgof> )

or even

=Average( <FieldtoCalcAvgof> ) where (<PrevFlag>="Curr")

digpen :us: (BOB member since 2002-08-15)

12

Yeah, this solution also sounds good.
I used the new solution to one of my count formula and it is not working.
The formula is as below, basically I am counting all the names from previous year and it just returns 1

=Count( If <YearFlag>="Prev" Then <Name> )

Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

13

Count (in Deski), by default is a count distinct. Is there only one name? :slight_smile:

What about:

=Sum( If <YearFlag>="Prev" Then 1 else 0  )

Sorry… forgot to mention you can also use:

=CountAll( If <YearFlag>="Prev" Then <Name> )

digpen :us: (BOB member since 2002-08-15)

14

Even Countall is not working.
The below formula should work,

=Count( If <YearFlag>="Prev" Then <Name> ) Where <Received Date> = Max(<PrevDateTest1>) In (<Name>)

but unfortunately it gives syntax error.
Even in the report itself there are more than 1 name for 2009.
Thanks,…

JohnJustus :us: (BOB member since 2007-06-25)

15

The WHERE clause cannot evaluate more than one condition. Is it possible to invert your formula?

Count( If <Received Date> = ( Max(<PrevDateTest1>) In (<Name>)) then <Name> ) where ( <YearFlag>="Prev")

digpen :us: (BOB member since 2002-08-15)

16

Thanks Digpen , that worked perfect.
One last thing, How about this YTD Formula. I am little suspicious , the numbers are coming and seems to be OK however, I wanted to confirm with the syntax.

AverageYTD=Average(If<Received Date>>RelativeDate(CurrentDate() ,-DayNumberOfYear(CurrentDate()))  Then <CurrentRankVar> Where( <PSL NA Core>="F1"))

Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

17

I think it’s fine. I would have tried to simplify the CurrentYear determining value, but it should work.

=Average(If Year(<Received Date>) = Year( CurrentDate() )  Then <CurrentRankVar> Where( <PSL NA Core>="F1"))

digpen :us: (BOB member since 2002-08-15)

18

Yeah , both returns the same number.
Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

19

Hi Digpen,
Just an update on this formula. If I use count function the number goes wrong,


=Count(If Year(<Received Date>) = Year( CurrentDate() )  Then <Name>)

I expect 18 but returns only 1 (Becuse the detailed report has 18 rows with Name) . Is the formula evaluating wrong? Also, I am placing the formula in a Cell.

Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

20

Have you tried changing the Count to CountAll?


=CountAll(If Year(<Received Date>) = Year( CurrentDate() )  Then <Name>)

digpen :us: (BOB member since 2002-08-15)