Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Partner
Partner

If Count Expression

Hello!

I don't know what I'm doing wrong with the following expression.  I'm probably not using the parenthesis correctly.  I've used this exact expression before, just without the count portion.

This time, I'm adding the count portion to the expression.  If a [filename] has a count of 0-10, then '0-10 Times', etc.

Please help me fix this:

=

If(count[filename]<=10,Dual('0-10 Times',0),

If(count[filename]>10 and count[filename] <=30,Dual('11-30 Times',1),

If(count[filename]>30 and count[filename] <=50,Dual('31-50 Times',2),

If(count[filename]>50,Dual('50+ Times',3),

))))

1 Solution

Accepted Solutions

Re: If Count Expression

So, this was getting used as a calculated dimension... try this

=Aggr(

  If(count([filename]) <= 10, Dual('0-10 Times', 0),

  If(count([filename]) > 10 and count([filename]) <= 30, Dual('11-30 Times', 1),

  If(count([filename]) > 30 and count([filename]) <= 50, Dual('31-50 Times', 2),

  If(count([filename]) > 50, Dual('50+ Times', 3)))))

, [filename])

View solution in original post

12 Replies

Re: If Count Expression

Is this by any chance used as a calculated dimension or a list box expression? If not, then may be try this

=If(count([filename]) <= 10, Dual('0-10 Times', 0),

  If(count([filename]) > 10 and count([filename]) <= 30, Dual('11-30 Times', 1),

  If(count([filename]) > 30 and count([filename]) <= 50, Dual('31-50 Times', 2),

  If(count([filename]) > 50, Dual('50+ Times', 3)))))

Partner
Partner

Re: If Count Expression

Hi Lea,

You missed ()

=If(count([filename]) <= 10, Dual('0-10 Times', 0),

  If(count([filename]) > 10 and count([filename]) <= 30, Dual('11-30 Times', 1),

  If(count([filename]) > 30 and count([filename]) <= 50, Dual('31-50 Times', 2),

  If(count([filename]) > 50, Dual('50+ Times', 3)))))


Thanks,

Arvind Patil

Partner
Partner

Re: If Count Expression

Hi Sunny,

It did not work.  The expression was marked as "OK", but the dimension is showing as "Invalid":OK Dimension.png

Invalid Dimension.png

To give you some more background on what I'm trying to do, I want to create a bar chart similar to this one:

example.png

where the count of the [filename] is grouped into 1-10 Times, 11-30 Times, etc.

Partner
Partner

Re: If Count Expression

Hi Arvind,

It did not work.  Please see my response to Sunny above.  Do you have any suggestions?

Re: If Count Expression

So, this was getting used as a calculated dimension... try this

=Aggr(

  If(count([filename]) <= 10, Dual('0-10 Times', 0),

  If(count([filename]) > 10 and count([filename]) <= 30, Dual('11-30 Times', 1),

  If(count([filename]) > 30 and count([filename]) <= 50, Dual('31-50 Times', 2),

  If(count([filename]) > 50, Dual('50+ Times', 3)))))

, [filename])

View solution in original post

Partner
Partner

Re: If Count Expression

Yes, it worked.

Thanks so much!

Calculated Dimension.png

Partner
Partner

Re: If Count Expression

Hi Sunny,

I need additional help with this formula.  I have this bar chart that I created with the formula that you provided:

2018-10-17 14_22_50-Enterprise Reports Usage - Report Usage (continued) _ Sheets - Qlik Sense.png

Now, if I make a selection and on this bar chart, for lets say, 31-50 Times, this works.  I get this result:

2018-10-17 14_24_38-Enterprise Reports Usage - Report Usage (continued) _ Sheets - Qlik Sense.png

But now if I make a  selection on the pie chart, for lets say, 2018, the bar chart changes:

2018-10-17 14_26_31-Enterprise Reports Usage - Report Usage (continued) _ Sheets - Qlik Sense.png

Instead of giving me these results, I would want there to be no columns shown on the bar chart because there are no longer any filenames in 2018 that were used 31-50 Times.

I understand why it's giving me these results - because we actually filtered for the "Filename" and not for "31-50 Times":

2018-10-17 14_30_40-Enterprise Reports Usage - Report Usage (continued) _ Sheets - Qlik Sense.png

How can I adjust the formula that you gave me earlier to fix this problem?

Thank you!

Re: If Count Expression

Try this

=Aggr(

  If(count({<Year>} [filename]) <= 10, Dual('0-10 Times', 0),

  If(count({<Year>} [filename]) > 10 and count({<Year>} [filename]) <= 30, Dual('11-30 Times', 1),

  If(count({<Year>} [filename]) > 30 and count({<Year>} [filename]) <= 50, Dual('31-50 Times', 2),

  If(count({<Year>} [filename]) > 50, Dual('50+ Times', 3)))))

, [filename])

Partner
Partner

Re: If Count Expression

No, that gave me this result:

2018-10-17 15_56_32-Enterprise Reports Usage - Report Usage (continued) _ Sheets - Qlik Sense.png

And I'll clarify that the problem doesn't just happen when I select a specific Year.  It also happens when I make a selection on any other field.