Count formula for unique values filtered

[ugurarslan]

Hi,

I have a aging table.

Dear all,

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

Seller ID	Car Type	On Hold	Current	Due 1-30	Due 31-60	Due 61-90	Due 91-120	Due 120+
1456	Opal	YES	$12.00	$-	$345.00	$-	$75.00	$-
3478	Ferd	NO	$45.00	$-	$566.00	$664.00	$-	$-
478382	Pirsche	NO	$-	$788.00	$7.00	$6,443.00	$-	$97.00
48392874	Opal	NO	$-	$9.00	$8.00	$67.00	$7.00	$-
247487	Opal	YES	$87.00	$-	$99.00	$8.00	$-	$-
47465	Hunda	NO	$789.00	$346.00	$6.00	$8.00	$7.00	$-
3928094	Pirsche	YES	$80.00	$9.00	$54.00	$99.00	$-	$9.00
278479874	Opal	NO	$-	$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

[jwjackso]

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]

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