Total Count of value over 5 week span

[ronaldovuiv]

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

[Anonymous]

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

[vishsaggi]

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]

May be

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

Regards,

Antonio

[vishsaggi]

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]

Need to fill in the values for this pivot table.

[ronaldovuiv]

Hi Antonio,

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

Regards,

Aaron

[Anonymous]

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 · Accepted solution]

May be this

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

[ronaldovuiv]

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