Count if values in two different fields are true

[#1]

Hi,

Please help me with this expression?

We have a table like below:

PBI_ID	PBI STATUS	PKE_ID	PKE_STATUS
PBI0000123	Completed	PKE0000375	Draft
PBI0000345	Assigned	-	-
PBI0000745	Completed	-	-
PBI0000274	In Progress	-	-
PBI0000927	In Progress	-	-
PBI0000455	Completed	PKE0000222	Assigned

If a PBI_ID is completed then a PKE_ID should be created, so I am trying to add a text box displaying which completed PBI_ID's does not have a PKE_ID.

Text box should show that there are 1 completed PBI_ID without a PKE_ID.

Please help me with this expression?

[tamilarasu]

Ruan,

Try,

=Count({<[PBI STATUS]={'Completed'},PKE_ID={"=Len(Trim(PKE_ID))=0"}>}PBI_ID)

[sujeetsingh]

how this PKEID is created ? It is present at your back end data set .

if(PBI STATUS='Completed' and len(PKE_ID)=0,1)

Below is the sample

[#4]

This only shows me a value of one.

Maybe I should have mentioned that there are two tables:

PBI Table - Has all PBI values

PKE Table - Has all PKE values + PBI_ID.

So these two tables connect via PBI_ID.

Therefore all values in PKE table does have a PKE_ID and not all values on PBI table has values.

To explain it better PBI=problem ticket. After a problem ticket is completed a user must have a PKE=known error. This data is stored on a different table (the PKE data).

[#5]

This brings up no values at all.

Should it not be as simple as:

=Count(if([PBI_STATUS]='Completed', PBI_STATUS and [PKE_STATUS]='NULL', PKE_STATUS))

But this calculation seems like it is adding instead of taking two filters in consideration.

There are 97 completed PBI's and 33 without a PKE. So value should show 33.

My calculation shows 100...

[ajsjoshua]

Hi,

=Count({<[PBI STATUS]={'Completed'},PKE_ID= - {"PKE_ID****"}>}PBI_ID)

Regards,

Joshua

[#7]

Thanks Joshua,

This shows me the values that does have a PKE (i also needed this one).

So how do I now show the values that does not have a PKE, the NULL values.

It does however show show those error line, do you know why this is?

[maniram23 · Accepted solution]

Hi,

This is help full for you.

=if([PBI STATUS]='Completed' and len(Replace(PKE_ID,'-',''))=0 ,1)

or

=count(if([PBI STATUS]='Completed' and len(Replace(PKE_ID,'-',''))=0 ,1))

[#9]

Thank you so much it worked!

I have no clue why you use the "len" function when I am not counting the lenght of a text, but it works!

Can you please help with counting the PBI's that does have a value?

I used:

=Count ({$< PBI_STATUS={'Completed'}>} PKE_STATUS)

But some PBI's have 2 PKE's and I dont want it counted then.

There are 55 PKE's but 3 of the PBI's have 2 PKE's so I only want to see 52.

For some reason the expression below works but it shows an error (squigly line) below:

[ajsjoshua]

hi,

try this

=Count(Distinct {<[PBI STATUS]={'Completed'},PKE_ID -= {"PKE_ID****"}>}PBI_ID)

or

=Count(Distinct{<[PBI STATUS]={'Completed'},PKE_ID ={"*"}-{"PKE_ID****"}>}PBI_ID)

Regards,

Joshua.