# Calculation Help for calculating unique transaction count

Hello team

i have a table where each transaction is validated again multiple rule . I want unique trasaction where execution status is true : using formula : distinct_count(ifelse({rule_execution_status}= ‘TRUE’ ,{aura_tran_key},NULL))

or concat the two column rule execution status and aura trasaction key and count where value is true , Not sure how to acheive this . Please there are 40 or 50 rules are checked each transaction . even if its one time true i want it to be under detected transaction category

Hey @deepa.singh ,
welcome to the QuickSight Community. Just for clarification: if any of the 50 rules are true or false you want NULL?
If I get it right, you have to write the rules in the ifelse out. Like: distinct_count(ifelse({rule_execution_status}= ‘TRUE’ AND rule2=‘TRUE’ etc… ,{aura_tran_key},NULL))

BR
Robert

2 Likes

Hello Robert ,

We cant check each rule because we have new rules added every day , the SQL query iam using to fetch this data is :select count (distinct aura_tran_key || rule_execution_status) from table_name
where rule_execution_status = ‘True’ please make a calculated field for this

We cant hard code rules

Hi Deepa Singh,

Find the below calculation:-
ifelse({rule_execution_status} = TRUE, 1, 0) * distinct_count(concat({aura_tran_key},{rule_execution_status}))

Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.

@manojbapatla

This calculation is not working

As per above SQL query I have written cal:distinct_count(ifelse({rule_execution_status} =TRUE, concat({aura_tran_key} , {rule_execution_status}), null))
If its error ,please share data.

Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.

This is the error

@deepa.singh , It’s not error but the way you are handling calculation is not right approach.
You can achieve distinct count simply by using
distinct_countIf({aura_tran_key}, {rule_execution_status} = “TRUE”)

This will count aura_tran_key when one of the rule execution is found to be TRUE. You can refer this dashboard on Arena

This is based on problem statement you have provided. In case you need more assistance , please create a sample dashboard with sample dataset showing your problem state using Arena and post it here. (Details on using Arena can be found here - QuickSight Arena )

@deepa.singh , Please confirm if the above calculation helped you achieve KPi counts .

Worked thanks for help