topic Re: CountIFs in QlikView

CountIFs

alwayslearning — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am looking to count the total number from fields using various conditions, within my script.

I am having issues with the basic

TextCount('Field A'), - Script is saying there is a syntax error

Also I am looking to count field A is it meets certain criteria from Field B And C

e.g.

Field A Field B Field C

a Yes 01/01/17

No 01/03/17

b Yes 15/03/17

So I may want to count 'Field A' if 'Field B' is 'Yes' and date is after '01/02/17', You can see that Field's also have Nulls or Blanks

Hope someone can advise on this

Re: CountIFs

sunny_talwar — Fri, 03 Feb 2017 13:19:19 GMT

May be these

1) TextCount([Field A])

2) TextCount({<[Field B] = {'Yes'}, [Field C] = {"$(='>=' & Date(MakeDate(2017, 2, 1), 'DD/MM/YY'))"}>}[Field A])

Re: CountIFs

alwayslearning — Fri, 03 Feb 2017 14:08:38 GMT

Hi Sunny,

I get a 'Invalid expression' issue with TextCount([Field A])

I've written: TextCount([Field A]) as Total

Is there anything that I need to do somewhere in the script? All my other created variables using various IFs and other formula are working.

Re: CountIFs

Anil_Babu_Samineni — Fri, 03 Feb 2017 14:22:20 GMT

May be this

Field A Field B Field C

a           Yes      01/01/17

             No       01/03/17

b           Yes     15/03/17

You have Data like above

So I may want to count 'Field A' if 'Field B' is 'Yes' and date is after '01/02/17', You can see that Field's also have Nulls or Blanks

Here, When you talk about 01/02/03, You need to do in script for missing dates then try like below

Count({<[Field B] = {'Yes'}, [Field C] = {">= MakeDate(17,02,01)"}>}[Field A])

Re: CountIFs

MK9885 — Fri, 03 Feb 2017 14:24:03 GMT

Maybe try this?

=Count({<FieldB = {'Yes'}, FieldC = {"=$(vMax)"}>}[Field A])

And store a variable

vMax as if(FieldC>'01/02/2017',FieldC)

Plus,

Below expression by Sunny is working for me.

TextCount({<[Field B] = {'Yes'}, [Field C] = {"$(='>=' & Date(MakeDate(2017, 2, 1), 'DD/MM/YY'))"}>}[Field A])

You can use either of those.

Re: CountIFs

sunny_talwar — Fri, 03 Feb 2017 14:37:54 GMT

Are you doing this in the script? You might need to use Group By statement for 1) and use if instead of set analysis for 2)

Re: CountIFs

alwayslearning — Mon, 06 Feb 2017 12:21:34 GMT

Hi,

I fixed the syntax error I was getting in my script.

In regards to the expressions, I was looking to have them written in the script and not the dashboard and they do not work for me in script but do work when I put them as expressions in the dashboard.

Thanks

Re: CountIFs

alwayslearning — Mon, 06 Feb 2017 12:29:22 GMT

Hi Sunny,

Yep I am doing this in the script

I've figured out 1. could you explain 2)

Re: CountIFs

sunny_talwar — Mon, 06 Feb 2017 12:31:30 GMT

Something like this

TextCount(If([Field B] = 'Yes', [Field C] >= MakeDate(2017, 2, 1), [Field A])) as BlahBlah,

Re: CountIFs

alwayslearning — Mon, 06 Feb 2017 12:38:30 GMT

Thanks Sunny and everyone else for the help.