Help with an expression?

[Anonymous]

Hi,

I have the following expression that I need to understand, can you please let me know what do mean each section:

= Count(Aggr(Only({$<ID = e({<[Closure Code]={'wrong module','Transferred to new module'}>})>} [Case Count]), AutoID))

I know that the on red are fields, but I need to know what does the blue words/letters. I need to add exclusions for other fields, can I just add a coma and will work?

Please advise.

[sunny_talwar]

Can you prep up some mock up data and try to explain what do you need as a result from your data?

[vishsaggi]

Just wondering, you might have tried.?

In neelams expression he is not using the State field exclusion inside the e() function..Did you try using State exclusion as separate exclusion like he mentioned.?

May be try this?

= Count(Aggr(Only({$< ID = e({<[Closure Code]={wrong module','Transferred to a new module'}>}),

                                      [State] = {'*'} - { 'Pre', 'Invalid'} >} [Case Count]) , AutoID))

[Anonymous]

Sure, let's say that I have this table (there should be further fields, but this is an example)

Purpose	YRMON	State	ID	Closure code	Total
Test	2017-06	Wrong module	1111	Completed	1
		Proceed	1112	Completed	1
		Proceed	1113	Pre	1
		Proceed	1114	Completed	1
Printing	2017-06	Transferred to a new module	1115	Completed	1
		Proceed	1116	Completed	1
Business	2017-06	Proceed	1117	Completed	1
		Proceed	1118	Completed	1
		Proceed	1119	Invalid	1
Sales	2017-06	Proceed	1120	Completed	1
		Proceed	1121	Completed	1

If I used the condition: = Count(Aggr(Only({$<ID = e({<[State]={wrong module','Transferred to a new module'}>})>} [Case Count]), AutoID)), it worked fine, the records with the state wrong module and transferred to a new module are being removed from the table. If I added the other expression separated by coma, the filters won't work. I will need a condition that will count everything that hasn't Wrong module or transferred to a new module on state field and pre and invalid on closure code.

Please let me know if this helps.

[vishsaggi]

So here technically your count should be 9 for first expression and 7 after excluding closure code. I am not sure if this is a good way of doing it. But may be you can try like?

= Count({$< ID = E({< State = {'Wrong module','Transferred to a new module'} >}) >} ID) -

   Sum(Aggr(Count({$< Closurecode =  {'Pre', 'Invalid'} >}  ID), ID))

Or

if you want to use Only()

= Count(Aggr(Only({$< ID = E({< State = {'Wrong module','Transferred to a new module'} >}) >} ID), ID)) -

   Count(Aggr(Only({$< Closurecode =  {'Pre', 'Invalid'} >}  ID), ID))

[sunny_talwar]

Few questions for you

1) A single ID can have multiple state?

2) What is AutoID here? You mention that in your expression but I don't see it in the sample data? Is it the same thing as ID?

3) What is the expected output based on the data that you have provided?

[Anonymous]

The second expression looks like working. I am testing it.

[Anonymous]

1) No, a single ID can only have 1 state

2) AutoID is the ID of the request, that is related to the State or Closure Code (I just removed the 'Auto' for make the example easiest)

3) The idea is that the expression count all the requests that have the State different to 'Wrong Module' and 'Transfer to a new module', and if the state is different then don't count the Closure codes equals to invalid or pre.

I mean, there are 5 requests

1 has WM as state

1 has Pre as closure code

1 has TTANM and invalid

The expression should count 2 records.

[sunny_talwar]

When you say 5 request? What is a request here? ID? I see 11 IDs... or is it Purpose? But again there are only 4 Purposes.... How do you determine a request in your supplied data?

[Anonymous]

In the example that I have provided, should only display 7 records (1112,1114, 1116,1117,1118,1120,1121)

[sunny_talwar]

This gives me 7

=Count(DISTINCT {<State -= {'Wrong module', 'Transferred to a new module'}, [Closure code] -= {'Pre', 'Invalid'}>} ID)