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))
Maybe this can help you already. If not maybe you could provide more context.
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
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.
@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 )