topic Re: Filter distinct Count() for only a condition in QlikView

Filter distinct Count() for only a condition

danosoft — Tue, 21 Nov 2017 09:22:54 GMT

Hi i need to filter my Count() only when a condition is verified, in details:

i need to Count, only items where my field ANNO_EMI = ANNO + 1

Re: Filter distinct Count() for only a condition

arvind_patil — Tue, 21 Nov 2017 09:45:16 GMT

Hi Daniele,

may be like this:

Count (Distinct {<ANNO_EMI ={'ANNO + 1'}>} PDR)

Thanks,

Arvind Patil

Re: Filter distinct Count() for only a condition

danosoft — Tue, 21 Nov 2017 10:45:32 GMT

I think there are an error becouse if i write like this it not show me nothing but if i write:

Count (Distinct {<ANNO_EMI ={'04'}>} PDR)

it show me the line..... i think the error is in the field ANNO + 1

Re: Filter distinct Count() for only a condition

sunny_talwar — Tue, 21 Nov 2017 15:21:26 GMT

May be this

Count(DISTINCT {<ANNO_EMI = {"$(=Num(ANNO+1, '00'))"}>} PDR)

Re: Filter distinct Count() for only a condition

danosoft — Tue, 21 Nov 2017 15:30:10 GMT

No, i think problem is in the Field ANNO, the formula not take it, becouse is i write :

Count(DISTINCT {<MESE_EMISSIONE = {"$(=Num(2+1, '00'))"}>} PDR)

it take me the value and design the line in the chart

Re: Filter distinct Count() for only a condition

sunny_talwar — Tue, 21 Nov 2017 15:32:23 GMT

May be there are multiple values and you need to use Max() or Min() function

Count(DISTINCT {<ANNO_EMI = {"$(=Num(Max(ANNO)+1, '00'))"}>} PDR)

Re: Filter distinct Count() for only a condition

danosoft — Tue, 21 Nov 2017 15:49:36 GMT

Look at the picture, it not show me nothing..... my field MESE_EMISSIONE is in my database with value format 01... 12 and MESE_COMPETENZA with the same format.... if i use that kind of formula with replace MESE_COMPETENZA with a number value, chart show me values

Count(DISTINCT {<MESE_EMISSIONE = {"$(=Num(2+1, '00'))"}>} PDR)

Re: Filter distinct Count() for only a condition

sunny_talwar — Tue, 21 Nov 2017 15:59:25 GMT

What do you get when you add this to a text box object?

=Num(Max(ANNO)+1, '00')

Do you get 03?

Re: Filter distinct Count() for only a condition

danosoft — Tue, 21 Nov 2017 16:42:55 GMT

I get all these values, like you see in the line chart

Re: Filter distinct Count() for only a condition

sunny_talwar — Tue, 21 Nov 2017 18:38:34 GMT

I was asking for this expression in a text box object, not a line chart?

Re: Filter distinct Count() for only a condition

danosoft — Wed, 22 Nov 2017 08:26:32 GMT

is the same, in the text box the value is: 13

Re: Filter distinct Count() for only a condition

sunny_talwar — Wed, 22 Nov 2017 12:13:14 GMT

So you set analysis is actually doing this

Count(DISTINCT {<ANNO_EMI = {"$(=Num(13, '00'))"}>} PDR)

This is because set analysis is evaluated once per chart. So the value you see in text box object, is the value used in the set analysis for the whole chart. May I know the reason you expect to see 2 + 1?

Re: Filter distinct Count() for only a condition

danosoft — Wed, 22 Nov 2017 13:15:59 GMT

but i not want to set 2 + 1 i want to set: MESE_COMPETENZA + 1

but it not start....

Re: Filter distinct Count() for only a condition

sunny_talwar — Wed, 22 Nov 2017 13:18:02 GMT

Don't think I understand. Sorry my friend

Re: Filter distinct Count() for only a condition

danosoft — Wed, 22 Nov 2017 13:20:32 GMT

my problem i need to put a chart where my value of Budget line is my MESE_EMISSIONE = MESE_COMPETENZA +1

Re: Filter distinct Count() for only a condition

sunny_talwar — Wed, 22 Nov 2017 13:33:20 GMT

Oh okay, try this

Above(Count(DISTINCT PDR))

Re: Filter distinct Count() for only a condition

sunny_talwar — Wed, 22 Nov 2017 13:33:36 GMT

Or this

Before(Count(DISTINCT PDR))

Re: Filter distinct Count() for only a condition

danosoft — Wed, 22 Nov 2017 13:35:47 GMT

but there i not see the filed MESE_EMISSIONE e MESE _COMPETENZA (those are columns in my table)

Re: Filter distinct Count() for only a condition

sunny_talwar — Wed, 22 Nov 2017 13:38:19 GMT

Check these

Qlik Community Tip: How to Get Answers to Your Post

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: Filter distinct Count() for only a condition

danosoft — Wed, 22 Nov 2017 13:41:14 GMT

thanks but i sent all from the first post.........