Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
How can I have the same condition with SUM Distinct?
I will need that the expression check both conditions and if one of them is true then don't count it.
Can you share you actual app you are facing issue? What is your expected output should be?
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?
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
Did you gave this a shot like Stefan mentioned?
=Count(DISTINCT {<[State]-={'Pre', 'Invalid'}>+<[Closure code] -= {'Test','Transferred'}>} ID)
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.
I did, instead of excluding is including, it's like the expression is not doing anything.
Alright, I am going to let Stefan and others do the talking here .
All the best!!
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.