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
robertocesp
Creator II
Creator II

How can I have the same condition with SUM Distinct?

robertocesp
Creator II
Creator II

I will need that the expression check both conditions and if one of them is true then don't count it.

vishsaggi
Champion III
Champion III

Can you share you actual app you are facing issue? What is your expected output should be?

robertocesp
Creator II
Creator II

I need to exclude the records that have some specific values as State or some specific values as Closure Code. I can't share my application.

Originally, I have the following expression:

SUM(Aggr(Only({$<ID = e({<[Closure Code]={'Test','Failed'}>})>} [Escalated Case Count]), ID))

Escalated case count is a field that will have 1 or 0.

But it excludes only the states, how can I add the or to also exclude the specific closure codes. If I use a code like this, it doesn't exclude anything:

SUM(Aggr(Only({$< ID = E({< [State]={'Pre', 'Invalid'} >}) >} [Escalated Case Count] ), ID)) - SUM(Aggr(Only({$< [Closure Code]={'Test','Transferred'} >}  [Escalated Case Count] ), ID ))

or this one

SUM(Aggr(only({<[State]-={'Pre', 'Invalid'}, [Closure code] -= {'Test','Transferred'}>}[Escalated Case Count]), ID))

Also this one, does not work at all:

= (Count(DISTINCT {<[State]-={'Pre', 'Invalid'}, [Closure code] -= {'Test','Transferred'}>} ID)) 

I need to exclude if one condition is true or the second condition. Is there a way to do this?

vishsaggi
Champion III
Champion III

Did you try what Stefan Suggested? And any specific reason you can't share your sample file. You can scramble your data if it is confidential. Coz that way you can get quick responses. It is tough to get what you want with assumption answers. Just follow these steps and share your sample with some expected output?

Preparing examples for Upload - Reduction and Data Scrambling

sunny_talwar

Did you gave this a shot like Stefan mentioned?

=Count(DISTINCT {<[State]-={'Pre', 'Invalid'}>+<[Closure code] -= {'Test','Transferred'}>} ID)

swuehl
MVP
MVP

Roberto Cespedes wrote:

How can I have the same condition with SUM Distinct?

Not sure I understand. But you can use the set analysis to filter the IDs within any aggregation function.

Note that my suggested set expression differs from what Sunny suggested last, i.e. it used the e() function to exclude IDs with certain states or certain codes.

My main point is that if you extend a set expression with more field modifiers, in general you are creating a smaller set (like making more selections in list boxes, this will decrease the available record set by filtering your records using more criteria). Applying the e() function like in your original sample on that set, you will get more IDs returned.

Does this sounds reasonable?

As far as I understand, you don't want the filter combined using AND logic, but OR logic, hence you want the union of two record sets each filtered by state resp. closure code. This (the union) will in general return a larger record set, hence the e() function applied will return a smaller record set than the version with just one filter.

robertocesp
Creator II
Creator II

I did, instead of excluding is including, it's like the expression is not doing anything.

sunny_talwar

Alright, I am going to let Stefan and others do the talking here .

All the best!!

swuehl
MVP
MVP

Roberto, I don't think I can follow your comments.

Please use Sunny's sample QVW to demonstrate your current status (i.e. adapt to your real setting as much as possible),

create your table chart and post the QVW together with your expected result.