topic Re: Remove Field Value in a Line Chart in QlikView

Remove Field Value in a Line Chart

Wed, 07 Jan 2015 17:37:44 GMT

Hi everyone!

I'm fairly new in Qlikview and I was wondering if anyone have any idea about how to remove a field value from a result.

By this, I mean I have a line chart which has two dimensions (date and export companies) and the expression is the total of tonnes exported in a year. The issue is that I want to ilustrate only 5 or 6 of these export companies in the line chart and I want to exclude the rest of the companies. I don't want to see them in the chart.

I tried with calculate dimensions (if conditions) but it doesn't work. I have the 5 companies I want in the chart but there's still "Other companies" I tried with this code (I don't have much knwoldege on programming in QV)

=if([Exporter]='A' or [Exporter]='B' or [Exporter]='C' or [Exporter]='D' or [Exporter]='E' or [Exporter]='F' or [Exporter]='G',[Exporter],Null())

Would anybody have any suggestions to my questions and where to go to learn the basics concepts of the type of programming I might need.

I'll be grateful if someone answer.

Regards

John

Re: Remove Field Value in a Line Chart

marinasbc — Wed, 07 Jan 2015 17:57:31 GMT

Hi John,

If I understand correctly:

there are several ways to do this but you can write a conditional expression:

if(match([Exporter], 'A', 'B', 'C', 'D', 'E', 'F', 'G'), sum(tons_exported))

you can also keep the calculated dimension or simplify it to if(match([Exporter], 'A', 'B', 'C', 'D', 'E', 'F', 'G'), [Exporter]) and then check Suppress When Value is Null

Good luck,

Marina

Re: Remove Field Value in a Line Chart

marinasbc — Wed, 07 Jan 2015 18:03:20 GMT

There is also Set Analysis in the expression (to simulate data selections). If that suits the case, you may also write:

including exporters:

sum( {< [Exporter] = {'A', 'B', 'C', 'D', 'E', 'F', 'G'} >} tons_exported )

excluding exporters:

sum( {< ~[Exporter] = {'other companies'} >} tons_exported )

Re: Remove Field Value in a Line Chart

Wed, 07 Jan 2015 18:09:23 GMT

First of all thank you Marina.

A question, where do I have to put that code, I mean where is the Set Analysis?

Re: Remove Field Value in a Line Chart

marinasbc — Wed, 07 Jan 2015 18:23:23 GMT

Well, the set analysis exists only in expressions, so in the Expressions tab create a new expression and write one of the two expressions. Set Analysis itself is the bold text below.

including exporters:

sum( {< [Exporter] = {'A', 'B', 'C', 'D', 'E', 'F', 'G'} >} tons_exported )

excluding exporters:

sum( {< ~[Exporter] = {'other companies'} >} tons_exported )

Hope it works

Marina

Re: Remove Field Value in a Line Chart

Wed, 07 Jan 2015 18:27:25 GMT

Marina, thank you very much!

I tried the second solution you gave me above on your first answer and it works.

Regards

John

Re: Remove Field Value in a Line Chart

Colin-Albert — Wed, 07 Jan 2015 18:35:40 GMT

The simplest solution is to add a listbox on the Exporter field and then select which exporters are displayed in the chart by selecting items in the listbox.

There are many QlikView tutorial videos available on the Qlik website and on YouTube.

There is also a link to download the QlikView tutorial on the start page of QlikView Desktop.

Re: Remove Field Value in a Line Chart

Wed, 07 Jan 2015 21:05:56 GMT

Marina:

I'm working on the same file anda I want to show as a Dashboard the amount of sales [US$] of certain company (as you remembered, companies belong are field values). I tried with some expressions but it gives me the total amount of exportations. I would like to have a text object with the sales of company C on 2013 and 2014. I tried this:

='$' & Num(sum([Fob (US$)]) , '####') &' M'

It's obvious that it won't appear the company C because I have not mentioned. How could I make this expression?

Regards

John

Re: Remove Field Value in a Line Chart

marinasbc — Thu, 08 Jan 2015 10:48:49 GMT

Hi John,

You can use the following expression in the textbox:

= money ( sum( {< Exporter = {'C'} >} [Fob (US$)] ) )

If the field isn't Exporter, then use the correct company field

The money function applies the default money format set on the first lines of the load script (the ones QV sets automatically). If you want to show $M, then write something like money(exp, '$#.##0M'). Obviously, the field [Fob (US$)] should already be in million dollars but if it isn't, divide the expression by 1000000.

Best Regards,

Marina

Re: Remove Field Value in a Line Chart

Thu, 08 Jan 2015 12:21:07 GMT

Hi Marina, thanks for your fast answer.

I put the code you just said and it works. Now, if I also want to add the FOB [US$] of this company but for a certain year,i.e:2014; (year is also a field), what I have to include to the code?

Regards

John