topic set analysis exclude based on records in field in QlikView

set analysis exclude based on records in field

Wed, 15 Jun 2011 10:21:37 GMT

Hi all

I am trying to sum a column but would like to do this only where it does not equal 'No: addendum' in another field. The total seems to work ok however it does not seem to display properly in the charts.

Sum({ $ - <IncludeMasterImagingTable={'No: Addendum'}>} [No of exams])

Any help would be much appreciated.

Thanks

Dan

set analysis exclude based on records in field

Wed, 15 Jun 2011 10:24:15 GMT

Try this below one

Sum({<IncludeMasterImagingTable -={'No: Addendum'}>} [No of exams])

set analysis exclude based on records in field

Wed, 15 Jun 2011 10:26:39 GMT

it does not seem to like the - character. am i putting it in wrong?

set analysis exclude based on records in field

Wed, 15 Jun 2011 10:53:55 GMT

it is - character only. when i`m poste formulae with set analysis it apears like underscore. its is chr(45).

- Sridhar

set analysis exclude based on records in field

Wed, 15 Jun 2011 11:01:33 GMT

HI Sirdhar

It does not seem to be working still. I may have to revist this tommorow and as a quick work around just set them to 0.

tahnsk for your help

Dan

Re: set analysis exclude based on records in field

Wed, 15 Jun 2011 11:37:31 GMT

Hi,

Check out the working example with this post.

You could do this by 2ways. Your expression would be as follows.

Sum({<IncludeMasterImagingTable -={'No: Addendum'}>} [No of exams])

Sum({<IncludeMasterImagingTable ={"=IncludeMasterImagingTable<>'No: Addendum'"}>} [No of exams])

Hope this helps you.

- Sridhar

set analysis exclude based on records in field

Wed, 15 Jun 2011 11:48:25 GMT

It still will not work. It must be something wrong with how i have it set up. I am going to have to get the bottom of this tommorow as what you have proposed obviosuly works fine!!!

Thanks for all your help and i will come back to the post tommorow.

Dan

Re: set analysis exclude based on records in field

Wed, 15 Jun 2011 11:57:23 GMT

Daniel, Post your sample data here, we will also check this for you.

- Sridhar

Re: set analysis exclude based on records in field

Wed, 15 Jun 2011 12:13:05 GMT

Here we go. I have jsut taken a subset and put it into excel. Basically i have a total activity box which dispalys the total activity summary of the current selections. I also have a chart with the dimension of 'exam code' and expresion for the total activity.

total activity = the sum no.exams but excluding ''No: Addendum'

Thank you so much for your time.

dan

Re: set analysis exclude based on records in field

Wed, 15 Jun 2011 13:11:24 GMT

Daniel,

Not sure why all my below expressions doesnt give values . Very odd.

Sum ({<IncludeMasterImagingTable = {"*"}-{"*Addendum*"} >}[No of exams])

Sum ({<IncludeMasterImagingTable -= {"No: Addendum"} >}[No of exams])

Sum ({<IncludeMasterImagingTable = {"=len(IncludeMasterImagingTable) = 0"} >}[No of exams])

Can you change in back end when IncludeMasterImagingTable field <> No: Addendum then give some flag and use that as filter to get your data.

But need to investigate and understand why all my expression not giving valaue (can some here please explain me to undersand this odd behaviour )

Sample application is attached with this post.

Thanks in advance

- Sridhar