Add another Filter in Set Analysis

[microwin88x]

Hello!

I have the following expression with Set Analysis:

COUNT ({$<ID_TIME_DATE#={">=$(='$(vDateFrom)')<=$(='$(vDateTo)')"}>} DISTINCT ACCOUNT_ID)

What I want is to add the following to that expression:

{1<ACCOUNT_ID>}

How could I do that?

Thanks you!!!

[swuehl]

What should the second set expression be good for?

[microwin88x]

Because I'm trying to make a % over the total. Something like this:

COUNT(DISTINCT SP_ID)

/

COUNT({1<SP_ID>} DISTINCT SP_ID)

So that I should have:

COUNT ({$<ID_TIME_DATE#={">=$(='$(vDateFrom)')<=$(='$(vDateTo)')"}>} DISTINCT ACCOUNT_ID)

/

COUNT ({$<ID_TIME_DATE#={">=$(='$(vDateFrom)')<=$(='$(vDateTo)')"}>} {1<ACCOUNT_ID>} DISTINCT ACCOUNT_ID)

But I don't know where to put the thing in bold...

[swuehl]

Replace the thing in bold with TOTAL qualifier when you need to ignore dimension.

Or remove the thing in bold and use set identifier 1 in your first set expression.

[maxgro]

maybe

COUNT ({$<ID_TIME_DATE#={">=$(='$(vDateFrom)')<=$(='$(vDateTo)')"}>} DISTINCT ACCOUNT_ID)

/

COUNT ({$<ID_TIME_DATE#={">=$(='$(vDateFrom)')<=$(='$(vDateTo)')"}>} DISTINCT total ACCOUNT_ID)

or

...

/

COUNT ({1<ID_TIME_DATE#={">=$(='$(vDateFrom)')<=$(='$(vDateTo)')"}>} DISTINCT total ACCOUNT_ID)

[eduardo_sommer]

You can use

count(distinct SP_ID)

/

count(TOTAL distinct SP_ID)

If you have any dimension you need to ignore in the total, specify it after the TOTAL modifier as in TOTAL<dim1, dim2>

Hope this helps you

Eduardo

[microwin88x]

Thanks, I used the following

COUNT ({$<ID_TIME_DATE#={">=$(='$(vDateFrom)')<=$(='$(vDateTo)')"}>} DISTINCT ACCOUNT_ID)

/

COUNT (TOTAL {$<ID_TIME_DATE#={">=$(='$(vDateFrom)')<=$(='$(vDateTo)')"}>} DISTINCT ACCOUNT_ID)

But I'm using that expression on a Pivot Table where there are some Nulls for the expression and I'm hiding those values, so I never reach the 100% because of the hidden nulls. Is there any way to count the total over all the values except the null?