topic STRAIGHT TABLE ISSUE in QlikView

STRAIGHT TABLE ISSUE

Sun, 11 Oct 2015 22:57:58 GMT

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

Re: STRAIGHT TABLE ISSUE

sunny_talwar — Sun, 11 Oct 2015 23:05:28 GMT

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)

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

Re: STRAIGHT TABLE ISSUE

Sun, 11 Oct 2015 23:13:38 GMT

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

Re: STRAIGHT TABLE ISSUE

sunny_talwar — Sun, 11 Oct 2015 23:15:49 GMT

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

Best,

Sunny

Re: STRAIGHT TABLE ISSUE

sunny_talwar — Sun, 11 Oct 2015 23:19:24 GMT

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?

Re: STRAIGHT TABLE ISSUE

Sun, 11 Oct 2015 23:23:33 GMT

NO Need month only for Max Year -1

Re: STRAIGHT TABLE ISSUE

sunny_talwar — Sun, 11 Oct 2015 23:26:51 GMT

Try to check if one of these work:

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

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

Re: STRAIGHT TABLE ISSUE

Sun, 11 Oct 2015 23:34:17 GMT

Doest work, it still gives me only max year

Re: STRAIGHT TABLE ISSUE

sunny_talwar — Sun, 11 Oct 2015 23:38:14 GMT

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

Re: STRAIGHT TABLE ISSUE

Sun, 11 Oct 2015 23:42:12 GMT

Dont see any attachment

Re: STRAIGHT TABLE ISSUE

Sun, 11 Oct 2015 23:44:39 GMT

Can you please email it to me at rsaeedga@gmail.com

Re: STRAIGHT TABLE ISSUE

sunny_talwar — Sun, 11 Oct 2015 23:57:07 GMT

sent in the mail. Also you can find it attached here: Re: STRAIGHT TABLE ISSUE