topic Re: Count in a certain date in QlikView

Count in a certain date

natisham — Wed, 28 Jun 2017 12:33:14 GMT

Hello,

Im trying to count Engine hours over 40 hours by week. So I just need it to read

Header 1	Header 2
Engine Hours over 40	7
Engine Hours under 2	5

It would count Machine Names with over 40 Engine hours per week.

Currently I have :

Count({$<[Engine on (h)]={"<2"}>}[Engine on (h)]) but it is giving me the total and not counting the number of weeks it ran under 2 hours.

Please help.

Re: Count in a certain date

Anil_Babu_Samineni — Wed, 28 Jun 2017 15:30:49 GMT

Better if you provide 5 rows data set and expected result set

Count({$<[Header 1] = {"=SubField([Header 1], ' ', -1) <= 2"}>} [Header 1])

Re: Count in a certain date

zebhashmi — Wed, 28 Jun 2017 15:42:24 GMT

Count({$<[Engine on (h)]={"<2"}>}[Engine on (h)])

Your formula is perfectly fine use it in KPI

Re: Count in a certain date

sunny_talwar — Wed, 28 Jun 2017 16:06:38 GMT

Is Header1 your field Engine on (h)?

Re: Count in a certain date

natisham — Wed, 28 Jun 2017 16:46:39 GMT

Yes Engine on (h) is my header. When I use Count({$<[Engine on (h)]={"<2"}>}[Engine on (h)]) i am getting a total of the hours instead of how many times it went over 40 in a week span

Re: Count in a certain date

natisham — Wed, 28 Jun 2017 16:49:48 GMT

Sorry meant under 2 instead of over 40

Re: Count in a certain date

sunny_talwar — Wed, 28 Jun 2017 17:24:21 GMT

Does Engine on (h) include numeric values? Can you share a screenshot of your Engine on (h) field?

Re: Count in a certain date

natisham — Wed, 28 Jun 2017 17:51:59 GMT

They are all Numeric values

Re: Count in a certain date

sunny_talwar — Wed, 28 Jun 2017 18:21:10 GMT

Screenshot?

Re: Count in a certain date

natisham — Wed, 28 Jun 2017 18:29:53 GMT

I have the table below (customer name has been changed):

The Numeric numbers are per weeks for Engine on (h)

Re: Count in a certain date

sunny_talwar — Wed, 28 Jun 2017 18:34:05 GMT

So then it appears that the if statement is working for you, isn't it? Are you looking to use set analysis to improve performance?

Re: Count in a certain date

natisham — Wed, 28 Jun 2017 18:51:03 GMT

Yes please , For every cell that is over 40 hours i need to count it as 1. Then add them all together for the month.