Using nested if with count

[#1]

Hello Everyone,

I'm trying to use a nested if in my count expression. It's not working , i think I'm lacking in the syntax somewhere. i would appreciate your help.

Expression:

count(if(COLOR='GOLD' and RECORD_FLAG='C',if(COLOR='RED' or COLOR='YELLOW' or COLOR='GREEN' and RECORD_FLAG='O', ID)))

Dimension: ID

So, basically I want to count total number of ID's , where if the color of the ID is gold and flag is 'C', it should further check the records of the same ID and if the  condition COLOR='RED' or COLOR='YELLOW' or COLOR='GREEN and RECORD_FLAG='O', is true, then it should count the ID.

Thanks. I look forward to the correct syntax.

[Anonymous]

I suspect that you want three color conditions in the internal if() to be checked  before the flag condition.  If yes, you need another set of ():

count(if(COLOR='GOLD' and RECORD_FLAG='C',if((COLOR='RED' or COLOR='YELLOW' or COLOR='GREEN') and RECORD_FLAG='O', ID)))

[#3]

Hi Micheal,

Thanks for replying. My data looks like :

ID , COLOR, FLAG

1,GOLD, C

1, RED, O

1, Yellow, O

So, basically I want to count the ID's first when the flag is 'C' and color is GOLD, then when this match is found, it should look down to other rows for the same ID and see if the condition

COLOR='RED' or COLOR='YELLOW' or COLOR='GREEN and RECORD_FLAG='O', is true, then it should count it.

Something like in SQL, we write:

Select count(*) from tabel1

where color='Gold' and Flag='C' and ID in

(Select ID from  Tabel1

where Color='Yellow' and Flag='O')

And I'm giving dimension as Months, and not ID's.

I hope I'm clear in my question?

Thanks a lot for your response.

[#4]

select count(*) from table where

record_flag = 'C' and color='Gold' and id in

( select id from table where record_flag='O' and( color= 'Green' or color= 'Yellow' or color= 'Red'))

Basically, I wanted to convert this SQL statement in to an expression in a chart. so, I can count these based on the monthly and quarterly basis, giving date range as dimension.

[johnw · Accepted solution]

I believe this:

count({<ID*=P({1<COLOR={'GOLD'},RECORD_FLAG={'C'}>} ID),COLOR*={'RED','YELLOW','GREEN'},RECORD_FLAG*={'O'}>} ID)

See attached.