topic Re: Help with selections please in QlikView

Help with selections please

Tue, 20 Aug 2013 08:52:15 GMT

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

Re: Help with selections please

Tue, 20 Aug 2013 09:08:17 GMT

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*

Re: Help with selections please

Tue, 20 Aug 2013 09:13:24 GMT

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.

Re: Help with selections please

Tue, 20 Aug 2013 09:59:36 GMT

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

Re: Help with selections please

Tue, 20 Aug 2013 10:23:18 GMT

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

Re: Help with selections please

Tue, 20 Aug 2013 10:42:02 GMT

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

Re: Help with selections please

Tue, 20 Aug 2013 10:44:56 GMT

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