Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alwayslearning
Creator
Creator

CountIFs

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

1 Solution

Accepted Solutions
sunny_talwar

Something like this

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

View solution in original post

9 Replies
sunny_talwar

May be these

1) TextCount([Field A])

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

alwayslearning
Creator
Creator
Author

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.

Anil_Babu_Samineni

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])

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MK9885
Master II
Master II

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.

sunny_talwar

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)

alwayslearning
Creator
Creator
Author

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

alwayslearning
Creator
Creator
Author

Hi Sunny,

Yep I am doing this in the script

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

sunny_talwar

Something like this

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

alwayslearning
Creator
Creator
Author

Thanks Sunny and everyone else for the help.