Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ugurarslan
New Contributor

Count formula for unique values filtered

Hi,

I have a aging table.


Dear all,

I have a aging balance (outstanding amounts) which looks like the following.

Seller IDCar TypeOn HoldCurrentDue 1-30Due 31-60Due 61-90Due 91-120Due 120+
1456OpalYES$12.00 $-   $345.00 $-   $75.00 $-  
3478FerdNO$45.00 $-   $566.00 $664.00 $-   $-  
478382PirscheNO$-   $788.00 $7.00 $6,443.00 $-   $97.00
48392874OpalNO$-   $9.00 $8.00 $67.00 $7.00 $-  
247487OpalYES$87.00 $-   $99.00 $8.00 $-   $-  
47465HundaNO$789.00 $346.00 $6.00 $8.00 $7.00 $-  
3928094PirscheYES$80.00 $9.00 $54.00 $99.00 $-   $9.00
278479874OpalNO$-   $8.00 $6.00 $7.00 $89.00 $56.00

The question which I want to answer is:

How many properties with a balance over 90 days due are on hold (YES) and how many not on hold (NO).

The answer should be in a table which has a

Dimension: ON Hold

Measure: ?????

Can you please help me, the formulas I try to write give me the wrong answers.

Thanks

Ugur

Tags (1)
2 Replies
jwjackso
Contributor III

Re: Count formula for unique values filtered

It depends on what your data model really looks like.

I used this formula for the count:  Count({<Period={'Due 91-120','Due 120+'}>}Distinct SellerID)

With a data  model like this:

Data:

Load * Inline [

SellerID,CarType,OnHold,Period,Amount

1456,Opal,YES,'Current',12.00

1456,Opal,YES,'Due 31-60',345.00

1456,Opal,YES,'Due 91-120',75.00

3478,Ferd,NO,'Current',45.00

3478,Ferd,NO,'Due 31-60',566.00

3478,Ferd,NO,'Due 61-90',664.00

3478,Ferd,NO,'Due 120+',97.00

478382,Pirsche,NO,'Due 1-30',788.00

478382,Pirsche,NO,'Due 31-60',7.00

478382,Pirsche,NO,'Due 61-90',6443.00

478382,Pirsche,NO,'Due 120+',97.00

];

markovalexsey
New Contributor II

Re: Count formula for unique values filtered

test_20180615 load other 2.png

Count({< [Due 91-120] = {'>0'} >} [Seller ID]) + Count({< [Due 120+] = {'>0'}>} [Seller ID])