Unable to run a formula with Countif nested in Ifelse statement

Question & Answer
, ,
1

I am trying to calculate (count({project_id}) - countIf({TcO Check},{TcO Check}=‘Date Missing’))/count({project_id}) for one business line, and if not to return a zero/null/blank. However, when I add the if else statement as follows, it returns an error.

ifelse({business_unit}=‘AMZL’,(count({project_id}) - countIf({TcO Check},{TcO Check}=‘Date Missing’))/count({project_id}),0)

Any suggestions ?

2

Hi @selam_tekie
Can you please check your expression? It seems to be missing the else expression and also AND between various conditions.

Please refer to the below link for more details on the ifelse syntax.
Ifelse - Amazon QuickSight

Regards
Vetri

3

@n_vetri thank you for taking the time to respond. The “0” is my else statement. This code works as it should on it’s own, it’s the if else that I cant figure out.

(count({project_id}) - countIf({TcO Check},{TcO Check}=‘Date Missing’))/count({project_id})

I want to say ifelse category = ‘something’ calculate (code above), else 0

4

Hey @selam_tekie - The formula you wrote above seems like it should work, however I dont see any ifelse’s in there (only the countIf). Are you trying to write an ifelse around this whole thing? Is even the part you shared above throwing an error?

5

Hi @Jesse. The countif formula works, but when I I put an ifelse around it (below), it throws an error.

ifelse({business_unit}=‘AMZL’,(count({project_id}) - countIf({TcO Check},{TcO Check}=‘Date Missing’))/count({project_id}),0)

6

I see. You cannot mix unaggregated fields (the if part of your iflese) with aggregated ones (the else part) in the same function.

Instead we can build the ifelse into each part of your ratio calc:

countIf({project_id}, {business_unit}=‘AMZL’) - countIf({TcO Check},{TcO Check}=‘Date Missing’ AND {business_unit}=‘AMZL’))
/
countIf({project_id}, {business_unit}=‘AMZL’)

Does that work?

1 Like
7

It worked. Thank you so much @Jesse

2 Likes