
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
IF(SUM())
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,
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Are you doing this in the script? Make sure you use Group by clause if you are doing this in the script

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
not in the data load editor

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Then where?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Each record under the visitCount has a value of 1 for a visit.
I created a table with the following field
id| ClientName| Start Year| Start Month| #ofVisits [which is sum(visitCount)], Morethan6VisitFlag
1 AA 2017 2 10 y
2 AB 2017 3 4 n
3 AC 2017 3 2 n
Separately I would like to create a flag when there are more than 6 visits.
Hope this helps,
if there is another method to do it, then I would be glad to learn.
Thank you

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this
If(Sum(visitCount) > 6, 'y', 'n')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be try this:
if(visitCount>6,'y','n')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi solomon.musayev, in this case if u mention that the fiel visitcount save number 1 for each visit i think the correct form for u solution would be this.
1. Do the load of Your table, for the fuield countvisit must be a count(), in order to count all the visits for each Clientname, for this the group by, and in the superior part of the table a precedent load giving it the codition for the flag.
TABLE_TEMP:
Load
*,
if(VISITCOUNTER>6,'Y', 'N') AS FLAG;
LOAD
id,
ClientName,
" Start Year",
"Start Month",
COUNT(visitcount) as VISITCOUNTER
FROM [table_source]
GROUP BY id, ClientName, " Start Year", "Start Month";
I could undertand this, if I am wrong please let me know.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I tried this before and it did not work.
Can if and sum be used together ie if(sum())
Has it worked in the past for you?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes it has.... can you share a sample or images where it isn't working?

- « Previous Replies
-
- 1
- 2
- Next Replies »