How to calculated values base on another value in the table

[pattdf]

Hi I’m looking for a way to make an analysis look similar to this but the ACTIVE of second stage “preprocessor” should equal to the number of COMPLETED from the first stage “ingestion” which is 375 and the ACTIVE of the next stage “data
_lake_staging” should equal to number of COMPLETED in the previous stage (375) and so on.

I put ACTIVE as countIf(table, status='active' AND stage='ingestion')
and have tried to include calculated fields COMPLETED in the formula

ifelse(stage='ingestion', countIf(table, status='active' AND stage='ingestion'), {COMPLETED})

got the error “Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.”

What’s the alternative way of doing this?

[Biswajit_1993]

hi @pattdf can you just remove your calculation and create a new calculated field and take only the CountIf function and not required to add the Ifelse condition statement please check with this if not able to do please confirm.

[pattdf]

The Countif itself works fine countIf(table, status='active' AND stage='ingestion') but in the other stage apart from ingestion will be 0 which is unlikely what I want. What I want is the other equal to COMPLETED which is another calculated field.

I’ve tried doing ifelse({stage}='ingestion' AND {status}='active',count({tableName}),{COMPLETED}). It gave the same error that aggregated and non aggregated can’t be combined.

[Max]

You need to utilize the same aggregation level across all of your calculated field. Here is how you can aggregate the stage / status.

ifelse(firstValue({stage},[{stage} ASC],[{stage}]) = ‘Ingestion’,…

I don’t know your Completed calculation but that needs to be aggregated as well.