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: 
Anonymous
Not applicable

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,

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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)

View solution in original post

11 Replies
sunny_talwar

Are you doing this in the script? Make sure you use Group by clause if you are doing this in the script

Anonymous
Not applicable
Author

not in the data load editor

sunny_talwar

Then where?

Anonymous
Not applicable
Author

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

sunny_talwar

May be this

If(Sum(visitCount) > 6, 'y', 'n')

prashanth1712
Contributor III
Contributor III

May be try this:

if(visitCount>6,'y','n')

Not applicable
Author

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.

Anonymous
Not applicable
Author

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?

sunny_talwar

Yes it has.... can you share a sample or images where it isn't working?