Count if scenario question

[davyqliks]

Hi All,

I am trying to make a KPI for a count with an if statement.

I have count of 5 total standard_name as a dimension and parent name as a dimension.

I need to count the parent_name where they are associated to none of the standard_name

I have tried like.

if(count(standard_name) = 0, Count(distinct parent_name))

which gives the right answer if in a table with parent_name, however when parent_name is removed i get a null.

so i try to Aggregated the function like below:

Aggr(if(count(standard_name) = 0, Count(distinct parent_name)),parent_name)

without parent_name in the table and i get - 1

when i add parent_name back to the table i get 7

I know the answer should be  as the above.

Can any let me know how i can get 7 in the KPI box please?

Thank you

Daniel

[BrunPierre]

Something like this perhaps

=Count({1<parent_name =E(standard_name)>} DISTINCT parent_name )

[davyqliks]

Hi Peter, thank you for the suggestion.

Unfortunately i get a 0 with this. May i ask the meaning of the =E?

thank you

[agigliotti]

Maybe this:

=Count( {1< parent_name = E( {< parent_name = {"=Count(standard_name)>0"} >} ) DISTINCT parent_name )

I hope it can helps.

Best Regards

[davyqliks]

Hi,

Thanks Alot Agigliotti,

Unfortunately the syntax is not quite right here, i tried to correct but cannot find the right way, are you able to help please?

it looks like a > is missing

=Count( {1< parent_name = E( {< parent_name = {"=Count(standard_name)>0">} >} ) DISTINCT parent_name )

but this did not resolve,

Again, really appreciate the help and any suggestions here

thanks

[marcus_sommer · Accepted solution]

I'm not sure if the issue is really connected to the condition - in this case a switch from an if-loop to a set analysis wouldn't be helpful - else it might be more associated to the aggregation. In your first attempts you used:

aggr(Exp, Dim)

but this isn't really a measure else a dimension. Therefore you may try it in this way:  

sum(aggr(Exp, Dim)

- Marcus