need help with eliminating records

[analytics2006]

I am working on a report and need to do the comparison between current type and previous type and the report is group by acct# then inv# and the formula (acct.type =previous(acct.type) is placed at the group footer /suppress (no drill down) / x-2. Since acct# X222 only has one record in the table and it was reading comparing the acct# C125 type (SS) and date (1/20/07) as the previous type and date. I want to eliminate any records that doesn’t have previous records to compared to but I am not sure how I can do it I have tried if statement but it doesn’t work. I will really appreciated if anybody can help me with this.


ACCOUNT TABLE:

Acct# inv# type date

C125 h1223 nz 8/20/07
C125 h1223 ss 1/20/07
X222 T56 tz 1/20/07

Current Output:

Acct# inv# curr_type curr_date prev_type prev_date

C125 h1223 ss 1/20/07 nz 8/20/07
X222 t56 tz 1/20/07 ss 1/20/07

Correct output should be the following:
Acct# inv# curr_type curr_date prev_type prev_date

C125 h1223 ss 1/20/07 nz 8/20/07




thanks

 

[lbass]

You can add a clause to the suppression formula like:

or
count({table.acct#},{table.acct#}) = 1

...to eliminate the cases where there is no previous record for an account. Not sure whether your formula is otherwise working. If it isn't please supply the formula.

-LB

 

[analytics2006]

Thanks for the help again.

I tried to add the following to the group footer (inv#) suppress formula but still didn't work. the only other two formula
that I have is the prevtype (previous({account.type})) and prevdate (previous({account.date})) and these two fields are placed at the inv# group section. I am not sure how I can fix this? It still doesn't output the correct data when ther eis no previous record for an account.
Can you please help?

1st try

{ACCOUNT.TYPE}=PREVIOUS({ACCOUNT.TYPE})
and ( COUNT({ACCOUNT.acctnum}, {ACCOUNT.acctnum})=1)

2nd try

{ACCOUNT.TYPE}=PREVIOUS({ACCOUNT.TYPE})
and ( COUNT({ACCOUNT.TYPE}, {ACCOUNT.acctnum})=1)

3rd try

{ACCOUNT.TYPE}=PREVIOUS({ACCOUNT.TYPE})
and ( COUNT({ACCOUNT.TYPE}, {ACCOUNT.INV})=1)

 

[lbass]

It should be "or".

{ACCOUNT.TYPE}=PREVIOUS({ACCOUNT.TYPE}) or
COUNT({ACCOUNT.acctnum}, {ACCOUNT.acctnum})=1

I'm not sure what you are trying to do with the account type field, but it's unclear whether you are having a problem with that part of the formula. If you are, please show a larger sample of data and what you would expect to occur.

-LB

 

[analytics2006]

thanks for you help. I am comparing current and previous type to see if the type have been modifidey by a user.
Your formula works and did eliminated the records that doesn’t have previous type for account come after another accounts which has a previous type.
If the 1st record within the table doesn't have the a previous type(like acct# x222),
it will still output x222 to the report. How do I correct this?

thanks again for all your help.


ACCOUNT TABLE:

Acct# inv# type date
X222 T56 tz 1/20/07
C125 h1223 nz 8/20/07
C125 h1223 ss 1/20/07
Z225 V5555 tt 1/20/07
W525 Q8588 cc 8/20/07
W525 Q8588 tt 1/20/07



current output

Acct# inv# prev_type curr_type date
X222 T56 tz 1/20/07
C125 h1223 nz ss 1/20/07
W525 Q8588 cc tt 1/20/07

 

[lbass]

The formula is not evaluating because there is no previous record, so change the formula to:

(
not onfirstrecord and
{ACCOUNT.TYPE}=PREVIOUS({ACCOUNT.TYPE})
) or
COUNT({ACCOUNT.acctnum}, {ACCOUNT.acctnum})=1

-LB

 

[analytics2006]

LB

You are so awesome. It worked. Thanks for all your help.

 

[analytics2006]

I need to add user on the report and I have been trying to get it to work and still no luck.

the new requirement is to only output user who changed the type besides user= zy5897 or wy5689 but I will also need to output the previous data and type for each corrspond record.
I have added to account.user <> ["zy5897", "wy5689"] the selection formulas record and I think this is why I am not getting the previous data and type
but I am not sure where I need to placed this criteria. I have also tried to place at the section expert (group # 2 inv#) and still didn't work.
((
not onfirstrecord and
{ACCOUNT.TYPE}=PREVIOUS({ACCOUNT.TYPE})
) or
COUNT({ACCOUNT.acctnum}, {ACCOUNT.acctnum})=1)
and account.user <> ["zy5897", "wy5689"]

Does anybody know how can I fix this??

thanks

ACCOUNT TABLE:

Acct# inv# type date user
W525 Q8588 tz 2/25/07 wy5689
W525 Q8588 tt 2/20/07 zy5897
W525 Q8588 cc 1/20/07 cx8888
W525 Q8588 tt 1/10/07 zy5897
W525 Q8588 th 11/20/06 cx8888



current output

Acct# inv# prev_type prev_date curr_type date
w525 Q8588 cc 8/20/07

the output I need is listed below:

Acct# inv# prev_type prev_date curr_type date
w525 Q8588 tt 1/10/07 cc 1/20/07

 

[lbass]

If you use:

account.user <> ["zy5897", "wy5689"]

...in report->selection formula->record, you should get the correct results by just using a suppression formula of:

(
not onfirstrecord and
{ACCOUNT.TYPE}=PREVIOUS({ACCOUNT.TYPE})
) or
COUNT({ACCOUNT.acctnum}, {ACCOUNT.acctnum})=1

-LB

 

[analytics2006]

Yes, it kind worked but it didn't generate the result I need because the select record.

the output I need is listed below: Actually I need to output the prev type =(tt) instead of th even though the user is =zy5897.
Any suggestion how i can fix this??

thanks

Acct# inv# prev_type prev_date curr_type date
w525 Q8588 tt 1/10/07 cc 1/20/07


current output
Acct# inv# prev_type prev_date curr_type date
w525 Q8588 th 11/20/06 cc 8/20/07

 

[analytics2006]

Can anybody please help me?? I really don't know how to fix this..

the output I need is listed below: Actually I need to output the prev type =(tt) instead of th even though the user is =zy5897.
Any suggestion how i can fix this??

thanks

Acct# inv# prev_type prev_date curr_type date
w525 Q8588 tt 1/10/07 cc 1/20/07


current output
Acct# inv# prev_type prev_date curr_type date
w525 Q8588 th 11/20/06 cc 8/20/07