Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
robertocesp
Creator II
Creator II

Help with an expression?

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.

40 Replies
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
Champion III
Champion III

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))

robertocesp
Creator II
Creator II

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

PurposeYRMONStateIDClosure codeTotal
Test2017-06Wrong module1111Completed1
Proceed 1112Completed1
Proceed 1113Pre1
Proceed 1114Completed1
Printing2017-06Transferred to a new module1115Completed1
Proceed 1116Completed1
Business2017-06Proceed 1117Completed1
Proceed 1118Completed1
Proceed 1119Invalid1
Sales2017-06Proceed 1120Completed1
Proceed 1121Completed1

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
Champion III
Champion III

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?

robertocesp
Creator II
Creator II

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

robertocesp
Creator II
Creator II

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?

robertocesp
Creator II
Creator II

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)


Capture.PNG