Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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,

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: IF(SUM())

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

10 Replies
Highlighted

Re: IF(SUM())

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

Highlighted
Contributor III
Contributor III

Re: IF(SUM())

not in the data load editor

Highlighted

Re: IF(SUM())

Then where?

Highlighted
Contributor III
Contributor III

Re: IF(SUM())

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

Highlighted

Re: IF(SUM())

May be this

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

Highlighted
Contributor III
Contributor III

Re: IF(SUM())

May be try this:

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

Highlighted
Not applicable

Re: IF(SUM())

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.

Highlighted
Contributor III
Contributor III

Re: IF(SUM())

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?

Highlighted

Re: IF(SUM())

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