topic Re: Total Count of value over 5 week span in QlikView

Total Count of value over 5 week span

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

Hi all,

I would like to create an expression that would give me the total number of yellow boxes (any value greater than 40) for all Test #'s in the example below. So the desired number for the example below would be 12. I am grateful for any suggestions.

KR,

Aaron

Re: Total Count of value over 5 week span

Anonymous — Tue, 27 Jun 2017 20:14:26 GMT

Do you need this expression in the front end or the back end?

Re: Total Count of value over 5 week span

vishsaggi — Tue, 27 Jun 2017 20:30:43 GMT

Do you want to display the number 12 or Count of yellow boxes in a text box? May be you can try like:

= Count(IF(Aggr(Sum(Value), WeekYear) > 40, 1))

Re: Total Count of value over 5 week span

antoniotiman — Tue, 27 Jun 2017 20:52:42 GMT

May be

Count(If(Sum(Aggr(Sum(Value),Example,Week)) > 40))

Regards,

Antonio

Re: Total Count of value over 5 week span

vishsaggi — Tue, 27 Jun 2017 21:00:43 GMT

Hello Antonio,

You saying you can use this expr in text box? If yes, may be a slight addition to your expression else might throw an error. As IF should return some value.

Can you check your expr will throw an error with Nested Aggr Not allowed or a script error. Just wondering.

= Count(Sum(Aggr(Sum(Value),Example,Week)) > 40))

Re: Total Count of value over 5 week span

ronaldovuiv — Thu, 29 Jun 2017 12:26:35 GMT

Need to fill in the values for this pivot table.

Re: Total Count of value over 5 week span

ronaldovuiv — Thu, 29 Jun 2017 12:28:54 GMT

Hi Antonio,

I at least get a value in return with this, but it gives me 5.

Regards,

Aaron

Re: Total Count of value over 5 week span

Anonymous — Thu, 29 Jun 2017 13:51:18 GMT

I would try to do it in a crosstable in the data load editor.

Crosstable(XX-2017, Hours,2)

Load

Example,

WeekYear,

18-2017,

19-2017,

ect.

resident tablename

then do the aggregation in the front end

Re: Total Count of value over 5 week span

sunny_talwar — Thu, 29 Jun 2017 13:57:21 GMT

May be this

Sum(Aggr(If(Sum(Value) > 40, 1, 0), Example, [Week Year]))

Re: Total Count of value over 5 week span

ronaldovuiv — Thu, 29 Jun 2017 16:51:06 GMT

Thank you Sunny this worked great! I tried it for =0 and <2 and it didn't work for some reason though.

Re: Total Count of value over 5 week span

sunny_talwar — Thu, 29 Jun 2017 16:57:00 GMT

I don't see why it wouldn't work for < 2, although 0 might be something related to the issue of missing or nulls. Difficult to say.