topic Re: Show specific records in table in QlikView

Show specific records in table

Anonymous — Mon, 02 Mar 2015 10:08:27 GMT

Hi guys,

I have a straight table which shows customers who have/haven't spent. To show this information I have used the following expression

=if(Match(sum({[No Spend]<[invoice_value]={'>0'}>} invoice_value),'0'),'No Spend')

This returns "No Spend" when not spent and a dash when spent.

Is it possible to show only the records in the table when they are set to "No Spend" ?

Thanks

Re: Show specific records in table

simospa — Mon, 02 Mar 2015 10:13:29 GMT

Hi,

try putting in every expression the condition you want to reduce your data, ie if you have an expression calculated as sum(amount):

=if(Match(sum({[No Spend]<[invoice_value]={'>0'}>} invoice_value),

sum(amount)

)

Let us know.

Re: Show specific records in table

Gysbert_Wassenaar — Mon, 02 Mar 2015 10:16:45 GMT

Yes. Can you post an example qlikview document?

Re: Show specific records in table

Roop — Mon, 02 Mar 2015 10:54:49 GMT

Yes it is possible - You just need to put the filter of the expression around all of the expressions used - This will ensure that all other expressions evaluate to zero and hence do not show.

I have attached an example that should help

All the best

Re: Show specific records in table

Anonymous — Mon, 02 Mar 2015 11:33:40 GMT

Hi guys,

Thanks for your help. I am not quite sure how to do this?

My other expressions for Range 1 and Range 2 are

Range 1

sum({Spend<[invoice_value]={'>0'}>} invoice_value)

Range 2

sum({[No Spend]<[invoice_value]={'>0'}>} invoice_value)

To explain the Spend and No Spend are two separate states.

Thanks

Re: Show specific records in table

sunny_talwar — Mon, 02 Mar 2015 11:40:15 GMT

Within you current chart you can use the label for your No Spend expression to put as a condition for other expressions:

Expression 1

If(not IsNull([LabelforNoSpend]), sum({Spend<[invoice_value]={'>0'}>} invoice_value))

Expression 2

If(not IsNull([LabelforNoSpend]), sum({[No Spend]<[invoice_value]={'>0'}>} invoice_value))

This will force these two expressions to be null every time your last column doesn't say No Spend.

HTH

Best,

Re: Show specific records in table

Mon, 02 Mar 2015 12:13:57 GMT

Hi,

You could use a calculated dimension with suppressed nulls, something like:

Aggr(if(sum(invoice_value)=0, Company),Company)

Regards