Help with selections please

[#1]

Hi,

I'm trying to select a range of 'Billing codes' where the 'Hours worked' x 20 is over 1000001 (£).

I want to do this on a Select in Field action when a user enters a sheet. They are clicking on a text box that says that they have X projects over 100k in value, when they select it, it'll open the sleet with those projects preselected.

My attempt is:

=If(([Hours Worked]* 20)>=100001,[Billing code])

Which doesn't do anything.

If I hardcode it to test it, the selection would look like this:  (10002203 | 22933445 | 33355444)

Any suggestions would be appreciated.

Thanks

Simon

[#2]

You can create in the script a second Billing Code dimension, but only with those billings higher than the amount you want. Then, in the text box that the user will click on, paste this action selection for the billing code field:

='('&concat(DISTINCT [High Billing code],'|')&')'

this way it will select all the billing codes that appear on that dimension.

*[High Billing code] is the second dimension I´ve told you about*

[#3]

Maybe better this way:

='('&Concat(aggr(only({<[Hours worked]={'>=1000001'}>}[Billing Code]), [Billing Code]), '|')&')'

This way you don't have to create a second dimension, although I think you might see the performance reduced.

[#4]

Thanks Losu, I see what you're doing by building the string and concating the billing codes with the |

It's returning blank at the moment, I've created a text object to show what's happening and I get ()

I'm using the following:

='('&Concat(aggr(only({<[Hours worked]*20={'>=1'}>}[Billing Code]), [Billing Code]), '|')&')'

which should return all billing codes with £1 or more against them.

do you know how I'd break this bit down further:

aggr(only({<[Hours worked]*20={'>=50000'}>}[Billing Code]), [Billing Code])

so I can try and diagnose why it's not returning any values.

I have this code working elsewhere:

=sum(aggr(DISTINCT
If(
Sum({<[Year]={$(vCurrentYear)}, [Project Status]={'In Progress', 'Complete or Cancelled', 'On hold or stalled'}>} [Hours Worked]*if(IsNull(Rate), 20, Rate))>=1000
AND
Sum({<[Year]={$(vCurrentYear)},  [Project Status]={'In Progress', 'Complete or Cancelled', 'On hold or stalled'}>} [Hours Worked]*if(IsNull(Rate), 20, Rate))<=2000,
1, 0)
,[Billing code]))
& ' projects £1k-£20k'

Which does a successful count, so I know that it can see and count the billing codes where the spend is >= £1 and that they exist as I get over 300 returned.

Thanks

Simon

[#5]

Hi Simon,

I think that the problem is here:

{<[Hours worked]*20={'>=1'}>}

You can not multiply field selections this way on a set analysis. It should be:

{<[Hours worked]={"<=$(=80*2)"}>}

[#6]

Hi,

That's confused me a bit, where's the 80*2 come from? Where would I do the >= check?

I need to be able to see [Hours worked] x 20 >= 1000 for example?

Thanks

[#7]

Sorry, the 80*2 was just an example.

[Hours worked] x 20 >= 1000   is the same as  [Hours worked] >= 1000/20

So the set analysis would be:

{<[Hours worked]={">=$(=1000/20)"}>}