cancel
Showing results for 
Search instead for 
Did you mean: 

Count If Else

Former Member
0 Kudos

Hi everyone, Along with some other fields I have a table in my report with the below two columns.

The ACCOUNT NUMBER in the table repeats, but is unique when the DECISION column is factored in.

I'm trying to find a way to count "Decision" once per Account number.

In a way I need to be able to do some kind of compare between all the decision before per a unique account number and count it base on value of the decision.

A unique account number can have up to three (3) instances (Decsions) in the table

So for example

If DECISION = "DENY" for any instance of that specific account number, ignore every other instance and add 1 to the DENY count bucket

Else if DECISION = "PAY" for all instances of a specific account number add 1 to the PAY count bucket

Else if DECISION <> "DENY" but Decision = "CHANGE" for any instances of a specific account number add 1 to the CHANGE count bucket

So in the end my count should look like this:

DENY = 2

PAY =  1

CHANGE = 2

I've tried using =Count([ACCOUNT NUMBER]) In([DECISION]) But that gave me higher numbers that it should. For example PAY ended up equaling 3 when it should have been 1. And i can't seem to get the sytax right for my if statments for all other attempts.

Any assistance will be greatly appreciated!!!

Accepted Solutions (1)

Accepted Solutions (1)

rakeshkumar_bhure
Participant
0 Kudos

Hi  Marlon,

Please follow below step to get desired count.

1.Create a formula variable "DecisionNo" as below. Make it as dimension.

     =If([Decision]= "PAY") Then 1 ElseIf([Decision]= "CHANGE") Then 2 Else 3

    

2. Create second formula varibale as "Max Decision" as below.

     =[Decision] Where([DecisionNo.] = Max([DecisionNo.] In([Account Number];     [Decision]))In([Account Number]))

    

3. Create third formula variable as "Detail Decision" as below. Make this as a detail of account number.

     =[Max Decision]

    

4. Now Put [Detail Decision] and Count([Account Number]) in vertical table. Make a filter on [Detail Decision] as is not Null.

    

5. Now you will see count what you want.

    

Regards

Rakesh

Former Member
0 Kudos

Hey Rakesh, Thanks! that worked. REALLY appreciated the detail response!!!

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Samuel,

Try this & let me know if it worked.

=count(Decision) foreach ([Account number])

Regards,

Naveen

Former Member
0 Kudos

Hi,

If you want distinct then

=Count([Decision];Distinct) In([Account number])

Or

=Count([Decision];Distinct) ForEach([Account number])

You can use where also for Specific selection.

Follow Below links for more help .