Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Hermann_83
Contributor
Contributor
‎2022-01-12 09:47 AM

Count if aggregated value is equal 0

Hi everyone

I have two tables: contracts and postings, both are linked by contract id. There can be none or several entries in postings for each contract.

I want to count the number of contracts with a certain status and no entry in postings. So I guess I need to aggregate the number of postings, but how am I able to put this as a condition in the set expression?

Aggregation of postings per contract ID, replacing missings with "0":

=if(isnull(Aggr(Count({<Posting_Transaktionsstatus_Zahlung={'Erfasst'},Posting_storniert={0}>}distinct [%Posting_UID]), [%Vertrag_UID])),0,Aggr(Count({<Posting_Transaktionsstatus_Zahlung={'Erfasst'},Posting_storniert={0}>}distinct [%Posting_UID]), [%Vertrag_UID]))

This is the count of contracts - how do I put the statement above in here to count only those with 0 postings?

Count({<Vertrag_Status = {'Annullation'}>}distinct [%Vertrag_UID])

Thanks in advance for your help.

788 Views
0 Likes
0 Replies