topic Re: Add another Filter in Set Analysis in QlikView

Add another Filter in Set Analysis

microwin88x — Mon, 12 May 2014 18:27:12 GMT

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!!!

Re: Add another Filter in Set Analysis

swuehl — Mon, 12 May 2014 18:29:10 GMT

What should the second set expression be good for?

Re: Add another Filter in Set Analysis

microwin88x — Mon, 12 May 2014 18:30:50 GMT

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...

Re: Add another Filter in Set Analysis

swuehl — Mon, 12 May 2014 18:37:42 GMT

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.

Re: Add another Filter in Set Analysis

maxgro — Mon, 12 May 2014 18:40:17 GMT

maybe

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

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

...

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

Re: Add another Filter in Set Analysis

eduardo_sommer — Mon, 12 May 2014 18:41:12 GMT

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

Re: Add another Filter in Set Analysis

microwin88x — Mon, 12 May 2014 18:56:21 GMT

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?