topic Re: Restrict data shown in Tablebox in QlikView

Restrict data shown in Tablebox

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have a query with QlikView.

I would like to apply the following criteria to my table box so that it only displays the sales which have CNR greater than 50 and AUM greater than 25. Please see sample data below.

Sales Member	Product	CNR	AUM
Sales1	Product1	20	30
Sales 2	Product2	30	40
Sales 3	Product3	70	30

So ideally only the data in bold should be shown as it meets the criteria.

Many thanks,

Esther

Re: Restrict data shown in Tablebox

Peter_Cammaert — Tue, 24 Oct 2017 22:49:06 GMT

CNR greater than 50? That's not what your picture shows.

Moreover, Table Boxes cannot be made to do that. They're just a bunch of glued-together listboxes without the option to enter expressions. Change your Table Box into a straight table, and include a dummy expression. You can now use Calculated Dimensions to specify the values to skip, or use set analysis to do the same.

Re: Restrict data shown in Tablebox

vishsaggi — Wed, 25 Oct 2017 02:04:17 GMT

As peter suggested you cant use Tablebox to reduce data. Try below using straight table chart.

Add Straight table with

Dimensions : Sales Member, Product

Expr1: = Sum({< CNR = {'>50'}, AUM = {'>25'} >}CNR)

Expr2: = Sum({< AUM = {'>25'}, CNR = {'>50'} >}AUM)

Re: Restrict data shown in Tablebox

Anonymous — Wed, 25 Oct 2017 04:51:46 GMT

hello esther da-silva

for this u can use set analyis in straight table

Sum({< CNR = {'>50'}*AUM = {'>25'} >}CNR)

Sum({< AUM = {'>25'}* CNR = {'>50'} >}AUM)

hope this will help u

regards

rohit lohani

Re: Restrict data shown in Tablebox

Anonymous — Wed, 25 Oct 2017 18:50:26 GMT

Hi Peter,

Thank you for your response.

Yes my mistake, sales 3 should have been bold instead. I will try the straight table.

Re: Restrict data shown in Tablebox

Anonymous — Wed, 25 Oct 2017 18:53:59 GMT

Hi Vishwarath,

Thank you for your response, I tried the above expression but there was an 'error in set modifier expression'.

Kind regards,

Esther

Re: Restrict data shown in Tablebox

vishsaggi — Wed, 25 Oct 2017 19:51:51 GMT

It worked for me with the same data i copied. Do you want to show where you getting the problem or show your set analysis expression?

Re: Restrict data shown in Tablebox

Anonymous — Wed, 25 Oct 2017 20:02:21 GMT

Hello,

I applied your expression to my actual field names. Please se error message below.

Re: Restrict data shown in Tablebox

Anonymous — Wed, 25 Oct 2017 20:05:41 GMT

There was an error in the above expression.

Re: Restrict data shown in Tablebox

vishsaggi — Wed, 25 Oct 2017 20:19:55 GMT

You have an extra CNR in your expression. Try this

= Sum({< "CNR ($M)" = {'>50'}, "AUM ($MM)" = {'>25'} >} "CNR $(M)" )

Re: Restrict data shown in Tablebox

vishsaggi — Wed, 25 Oct 2017 20:22:35 GMT

Try this

= Sum({< [CNR ($M)] = {'>50'}, [AUM ($MM)] = {'>25'} >} [CNR $(M)] )

Re: Restrict data shown in Tablebox

Anonymous — Wed, 25 Oct 2017 21:10:23 GMT

The expression is ok now thank you

Re: Restrict data shown in Tablebox

vishsaggi — Wed, 25 Oct 2017 21:23:47 GMT

Cool. Can you close the thread accordingly.

Re: Restrict data shown in Tablebox

Anonymous — Wed, 25 Oct 2017 21:49:30 GMT

The formula appears to work but it does not match the data that that I used to cross check the result in my excel file

Re: Restrict data shown in Tablebox

Anonymous — Wed, 25 Oct 2017 21:50:25 GMT

For example I am getting 0's under the AUM field where there should be figures

Re: Restrict data shown in Tablebox

vishsaggi — Wed, 25 Oct 2017 23:42:27 GMT

can you share a sample qvw file to look into and your expected output based on your original data.

Re: Restrict data shown in Tablebox

Anonymous — Thu, 26 Oct 2017 01:07:05 GMT

I got it to work now, thank you

Re: Restrict data shown in Tablebox

Anonymous — Fri, 17 Nov 2017 03:59:13 GMT

It was an issue with my data source. Thanks