on 03-04-2014 7:18 PM
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!!!
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Samuel,
Try this & let me know if it worked.
=count(Decision) foreach ([Account number])
Regards,
Naveen
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.