Multiple Phase Formula

building reports desktopintelligence
1

Good Day,

I am bringing data back for users, a user could have had multiple entries for activity in one of 3 time periods. I need to get a count of these users in the following situations:

If a user does not show up in period 1 but shows up in period 2 they are considered new.

If a user does not show up in period 1 but shows up in period 2 as well as period 3 they are considered converted.

If they are in period 1 they are current users.

I have tried creating a boolean for each period but I can’t have the multiple values in the where clause because the “grouping” appears to be done after the scan for the table.

Thank you for the continued support to everyone ! Greg
2B||!2B

Listserv Archives (BOB member since 2002-06-25)

2

hi Greg,
I hope this what your formula should do:(<period1=true> means if a user shows up in period1)

If period1=true Then
Current
Else
If preiod3=true Then
converted
Else
new
Endif
Endif

If the above logic is correct you should create an object with this select: decode(period1,True,current,decode(period3,true,converted,new))

Hamid

Listserv Archives (BOB member since 2002-06-25)

3

Thank you Hamid,

The if then else would be more like:

If period1 = true Then
Current
Else If period2 = true Then
New
End If

If period2 = true And period 3 = true Then
Convert
End If

It is possible for period3 to be the only one true.

What I am failing to understand is how to determine whether both instances are true when the instances are on different rows.

BTW… Where do I find the syntax for decode() ?

Thank You,
Greg
2B||!2B

If period1=true Then
Current
Else
If preiod3=true Then
converted
Else
new
Endif
Endif

decode(period1,True,current,decode(period3,true,converted,new))

Listserv Archives (BOB member since 2002-06-25)

4

Greg,

you will need three neested decode then: decode(period1,True,current,decode(period2,true,decode(period3,true,convert, new),Current))

An example of decode:
decode(A,B,True,False) this means if A=B then True else False

I don’t have the exact syntax for DECODE, but a your DBA should have it.

Hamid

Listserv Archives (BOB member since 2002-06-25)

5

Thank you David, I created the queries using Sums in conjunction with if and thens. I am testing the results now.

Greg
Charlotte (704) 329-6930
Home (704) 882-6238
2B||!2B

Listserv Archives (BOB member since 2002-06-25)

6

In a message dated 00-02-11 11:48:45 EST, you write:

BTW… Where do I find the syntax for decode() ?

Decode is a database function. What you need is a report function if you want to process the data on the report itself.

I have not tried to figure out an answer yet, but what you will need to look at in the BusObj document is the Previous() function. You will have to look from one row to the previous to determine the status of a customer.

I hope to have some time to provide better directions later. But for now, that gives you something to start with.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

7

Hi Greg,
When looking for things that do not occur within a given time period, the trick is to use the “Exists” or “Not Exists” in your query. If you use SQL 7, I can give you some examples.
Simon

Listserv Archives (BOB member since 2002-06-25)

8

In a message dated 00-02-11 11:15:46 EST, you write:

If a user does not show up in period 1 but shows up in period 2 they are
considered new.

If a user does not show up in period 1 but shows up in period 2 as well as period 3 they are considered converted.

If they are in period 1 they are current users.

To try to solve this problem, I created the following data in Excel:

Joe Period 1
Joe Period 2
Joe Period 3
Sue Period 3
Frank Period 2
Freddy Period 1
Mary Period 1
Mary Period 3
Gene Period 2
Gene Period 3

I created the following variables:

Variable: Count 1
Formula: =Count() Where (=“Period 1”)

Variable: Count 2
Formula: =Count() Where (=“Period 2”)

Variable: Count 3
Formula: =Count() Where (=“Period 3”)

Variable: Status
Formula: = If (IsNull(<Count 1>) And <Count 2>=1 And IsNull(<Count 3>)) Then “New” Else If <Count 1>=1 Then “Current User” Else If (<Count 2>=1 And <Count 3>=1) Then “Converted” Else “Undefined”

Given the problem statement:

In a message dated 00-02-11 11:15:46 EST, you write:

If a user does not show up in period 1 but shows up in period 2 they are
considered new.

If Count 1 is null and Count 2 = 1, then “New”

If a user does not show up in period 1 but shows up in period 2 as well as period 3 they are considered converted.

If Count 1 is null and Count 2 = 1 and Count 3 = 1 then “Converted”

If they are in period 1 they are current users.

If Count 1 = 1 the “New”

Anything else: “Undefined”

I can send you the Excel file and the BusObj file if you like, but based on the limited description of your problem it appears that this will work. You didn’t really say how your data was formatted, so I guessed. :slight_smile:

The Count() Where (=“value”) returns a count of how many times that status appears for that name. You would, of course, need to build the formulas based on your own data.

Once the counts are done, then it becomes a matter of evaulating each count as per the definition of the problem.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)