A multiple conditioned count

Question

I have a list of references in a database. If the reference exists in a month then it is rated either a 1 or a 99. If the reference does not exist then it gets a rating of 0. Each 3 months forms a quarter. In the rich client I am able to create the following cross-table:

    | Q1    Q1    Q1  |  Q2    Q2    Q2
Ref | M1    M2    M3  |  M4    M5    M6
----|-----------------|-----------------
  a | 99    99    99  |  99    01    99
  b | 00    01    99  |  99    01    00
  c | 99    00    00  |  00    00    00
  d | 99    01    01  |  01    01    99
  e | 00    00    01  |  01    99    01
  f | 01    01    01  |  99    99    99
  g | 01    01    01  |  00    00    00
  h | 99    01    00  |  00    00    00
  i | 00    00    01  |  01    99    01
  j | 00    00    00  |  00    01    99
  k | 00    00    00  |  01    01    01

What I want to do is for each quarter, count the total references that existed at some point in that quarter and also have a count of any reference that ended with a rating of 99. Now by ended I mean the last rating that was given in the quarter.

E.g. Ref a for Q1 existed and would also count for 99. Ref h for Q1 also existed and its final rating would be considered a 1. Ref j would not be counted at all in Q1.

The full outcome would be:

Ref | Last Q1 | Last Q2
----|---------|---------
  a |   99    |    99
  b |   99    |    01
  c |   99    |    00
  d |   01    |    99
  e |   01    |    01
  f |   01    |    99
  g |   01    |    00
  h |   01    |    00
  i |   01    |    01
  j |   00    |    99
  k |   00    |    01

Which gives me a final count of:

Q          | Q1 | Q2
-----------|----|---
99 Count   | 3  | 4
Full Count | 9  | 8

What business objects code can jump straight to the count table?

I think the key is getting a bit of code to calculate the 'final rating' per customer per quarter, however I've had no luck doing that. I haven't found a way of using multiple 'where' clauses in one formula.

Answer 1

You'll need to use multiple variables to accomplish this.

Assuming you're starting out with objects named:

      | [Qtr]
      | [Mth]
  ----|------------
[Ref] | [Val]

1) Create a variable to hold the max month per quarter with a non-zero value:
MaxM = Max([Mth]) In ([Ref];[Qtr]) Where ([Val] <>"00")

Put this into the body of the crosstab to validate the result:

    Q1  Q2
a   M3  M6
b   M3  M5
c   M1  
d   M3  M6
e   M3  M6
f   M3  M6
g   M3  
h   M2  
i   M3  M6
j       M6
k       M6

2) Create another variable to count the number of 99s where the month is equal to the last month in the quarter with a value:
LastIs99Cnt = If (Max([Val]) Where ( [Mth] = [MaxM])) = "99" Then 1 Else 0

Replace [MaxM] in the crosstab with this variable so we can validate:

    Q1  Q2
a   1   1
b   1   0
c   1   0
d   0   1
e   0   0
f   0   1
g   0   0
h   0   0
i   0   0
j   0   1
k   0   0

3) Remove [Ref] from the report body and aggregate [LastIs99Cnt] with:
=Sum([LastIs99Cnt] ForEach ([Ref])) Also add a simple Count/Where to get the non-99 values:
=Count([Ref] Where ([Val] <> "00"))

The block will look like this:

         | [Qtr]
         |---------------------------
99 Cnt   | =Sum([LastIs99Cnt] ForEach (Ref))
Full Cnt | =Count(Ref] Where (Val] <> "00"))

And produce the desired result:

         Q1   Q2
99 Cnt    3    4
Full Cnt  9    8