STRAIGHT TABLE ISSUE

[#1]

I am trying to calculate the Average of  Orders which is the 

(Count of Orders last Years Dec + Count of Orders Max(Year and MOnth))/2

The below expression works fine, but when I put them in the Straight table and add Year as dimension It gives me both (Max Year and Max(Year)-1).  I would like to see only Max Year but with the below average.

Whats the best way to do it

(Count({<Year = {$(=Max(Year)-1)},Month={'Dec'}>} DISTINCT Orders)

+

Count({<Year = {$(=Max(Year))}>} DISTINCT Orders))/2

[sunny_talwar]

I think since you are using Year as a dimension, you would need to use Above() or Below() function here together with RangeSum().

May be something like this:

If(Year = Max(TOTAL Year), (RangeSum(Count(DISTINCT Orders), Above(Count(DISTINCT Orders))))/2)

or

(RangeSum(Count({<Year = {$(=Max(Year))}>} DISTINCT Orders), Above(Count({<Year = {$(=Max(Year)-1)},Month={'Dec'}>} DISTINCT Orders))))/2

[#3]

Which one out of the above is Max Year and which one  is the MaxYear -1

[sunny_talwar]

Didn't realize there was a Month here as well. Can you share some sample data to look at?

Best,

Sunny

[sunny_talwar]

Both of the expression should give you the same result:

1st Expression:

Max Year: Count(DISTINCT Orders)
Max Year - 1: Above(Count(DISTINCT Orders))

2nd Expression:

Max Year: Count({<Year = {$(=Max(Year))}>} DISTINCT Orders)

Max Year -1: Above(Count({<Year = {$(=Max(Year)-1)}, Month={'Dec'}>} DISTINCT Orders))

Do you need Month = {'Dec'} for both Max Year and Max Year - 1?

[#6]

NO Need month only for Max Year -1

[sunny_talwar]

Try to check if one of these work:

If(Year = Max(TOTAL Year), (RangeSum(Count(DISTINCT Orders), Above({<,Month={'Dec'}>}Count(DISTINCT Orders))))/2)

or

(RangeSum(Count({<Year = {$(=Max(Year))}>} DISTINCT Orders), Above(Count({<Year = {$(=Max(Year)-1)},Month={'Dec'}>} DISTINCT Orders))))/2

[#8]

Doest work, it still gives me only max year

[sunny_talwar · Accepted solution]

See if this application helps you understand what I am trying to say. This does not have Month = {'Dec'} filter because I did not add Month field in the application. But you should get an idea.

There are 5 orders in 2014 and 4 in 2015 and there average next to 2015 comes out to 4.5.

Best,

Sunny

[#10]

Dont see any attachment