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: 
ronaldovuiv
Contributor II
Contributor II

Total Count of value over 5 week span

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

Test1.PNG

1 Solution

Accepted Solutions
sunny_talwar

May be this

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

View solution in original post

10 Replies
Anonymous
Not applicable

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

vishsaggi
Champion III
Champion III

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

antoniotiman
Master III
Master III

May be

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

Regards,

Antonio

vishsaggi
Champion III
Champion III

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

ronaldovuiv
Contributor II
Contributor II
Author

Need to fill in the values for this pivot table.

Test2.PNG

ronaldovuiv
Contributor II
Contributor II
Author

Hi Antonio,

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

Regards,

Aaron

Anonymous
Not applicable

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

sunny_talwar

May be this

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

ronaldovuiv
Contributor II
Contributor II
Author

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