topic Re: Count greater than and less than in QlikView

Count greater than and less than

natisham — Fri, 10 Nov 2017 14:23:52 GMT

Having an issue finding 3 proper expression to find the amount of cells that have >40, <2, and =0 in qlikview.

Need to have it only read within my variable date ({<[END DATE]={'$(=$(=VMax_CY))'}>}[.

My table is below.

So from this image i need the answer to be:

>40 = 37

<2= 7

=0 = 3

Thank you.

sunny_talwar — Fri, 10 Nov 2017 14:28:49 GMT

Would you be able to share your qvw file?

natisham — Fri, 10 Nov 2017 14:29:35 GMT

sorry i cant for confidential purposes.

sunny_talwar — Fri, 10 Nov 2017 14:45:46 GMT

May be see if this can address your confidentiality concerns

natisham — Fri, 10 Nov 2017 15:35:27 GMT

I followed the instructions but it still shows more data them I am allowed to share.

Really just need if I was to use a straight table or even a text box what expression could I use to find the correct count or sum.

I have tried:

=Sum(Aggr(If(Sum([Engine on (h)]) > 40, 1, 0), Product, WeekYear)) with no luck

and =sum({<[END DATE]={"$(= '<=' & $(=VMax_CY)&'>=' &$(=VMin_CM))"}>}(Aggr(If(Sum([Engine on (h)]) > 40, 1, 0), Product, WeekYear))) with no luck

It is a monthly report broken out by week. Need to count the amount of times each product had a week with Engine Hours over 40

sunny_talwar — Fri, 10 Nov 2017 15:48:40 GMT

Is [Engine on (h)] in hours? May be try this

=Sum(Aggr(If(Sum([Engine on (h)]) > MakeTime(40), 1, 0), Product, WeekYear))

natisham — Fri, 10 Nov 2017 15:51:41 GMT

Engine Hours are in hours but not by time. When I use the expression I get 0.

sunny_talwar — Fri, 10 Nov 2017 16:05:28 GMT

Don't know natisham‌, I want to help, but don't have enough info to help here

craig157 — Fri, 10 Nov 2017 16:49:25 GMT

Have you tried using something similar to excels Countif function?

So like:

if(WeekYearField={variableDate},Count({WeekYearField={"=>40"}

I am aware I am missing some punctuations and clarity here but hope it points you in the right direction.