IF(SUM())

[Anonymous]

Hello,

I have a field called visitCount.  Using this field, I calculate sum of visit that client had ie  sum(visitCount).

I would like to create a flag, that would allow me to select client that had more that 6 visits.

I have been trying to  --         if(sum(visitCount) > 6, 'y', 'n') but it has not worked.

Can anyone suggest an alternative method of tackling this issue.

Thank you,

[swuehl · Accepted solution]

Try creating a filter pane with a calculated dimension / field like

=Aggr( If(Sum(visitCount) >6, 'y','n') , id)

This will group your records by id field ( as I understood, id is a field that you used as dimension in your table chart, that identifies each row, maybe you can also use ClientName instead) and calculates the Sum(visitCount) per id. Just like a temporary table.

Result should be two values in your filter pane, 'y' and 'n'. Selecting one of the values should filter your id's.

Hope this helps,

Stefan

edit:

and if you need to ignore certain selections that limit the ids, you need to use set analysis in the Sum(), for example to ignore all selections:

=Aggr( If( Sum({1} visitCount) >6, 'y','n') , id)

[Tribhuwan]

Thanks a ton Man...your solution is really very helpful for me.