topic Re: If Count Expression in App Development

If Count Expression

ltc — Tue, 16 Oct 2018 22:33:30 GMT

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),

))))

Re: If Count Expression

sunny_talwar — Wed, 17 Oct 2018 00:50:49 GMT

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)))))

Re: If Count Expression

arvind_patil — Wed, 17 Oct 2018 04:42:24 GMT

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

Re: If Count Expression

ltc — Wed, 17 Oct 2018 15:37:46 GMT

Hi Sunny,

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

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

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

Re: If Count Expression

ltc — Wed, 17 Oct 2018 15:38:35 GMT

Hi Arvind,

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

Re: If Count Expression

sunny_talwar — Wed, 17 Oct 2018 15:41:48 GMT

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])

Re: If Count Expression

ltc — Wed, 17 Oct 2018 15:51:44 GMT

Yes, it worked.

Thanks so much!

Re: If Count Expression

ltc — Wed, 17 Oct 2018 21:33:11 GMT

Hi Sunny,

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

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

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

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":

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

Thank you!

Re: If Count Expression

sunny_talwar — Wed, 17 Oct 2018 22:55:40 GMT

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])

Re: If Count Expression

ltc — Wed, 17 Oct 2018 23:01:50 GMT

No, that gave me this result:

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.

Re: If Count Expression

sunny_talwar — Thu, 18 Oct 2018 10:39:17 GMT

Would you be able to share a sample where we can see the issue?

Re: If Count Expression

sunny_talwar — Thu, 18 Oct 2018 10:41:01 GMT

Also, you might be able to try this and see if this works

=Aggr(

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

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

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

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

, [filename])

=Aggr(

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

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

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

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

, [filename])

Re: If Count Expression

ltc — Thu, 18 Oct 2018 15:35:00 GMT

Yes, the last one worked.

I made a mistake with the suggestion you gave me yesterday. I forgot that I changed the field "filename" to "Filename" with an uppercase F. So that was the problem yesterday. It's possible that the solution you gave me yesterday worked but it was my oversight with the field name change. I'm going to use the last one though since I just tried it and it works.

Thank you SO much!!