topic creating charts like Excel in QlikView

creating charts like Excel

Anonymous — Mon, 22 Aug 2011 10:26:22 GMT

Hi,

I'm trying to create a chart similar to that in Excel (see attachment). When I look at the Excel chart it has two bars, one with a target and another one with the actual time per shipment.That goes same goes for the pie chart.

When I look at my Qlikview file I have a field with LeanTime. This calculates the amount of days for shipments. The maximum leantime per shipment should not be more than 8 days. Futhermore the bar chart starts at 90%. Where can I set this up in Qlikview?

How can I simulate this in Qlikview? Should I create an expression? For example on with (if below 8 day, then on time)

Hope you can help me out.

Thanks advance!

Regards,

iSam

creating charts like Excel

kaushiknsolanki — Mon, 22 Aug 2011 10:46:30 GMT

Hi,

If the maximum lean time is fix then you can do this calculation in script itself.

Say for example.

Load,

Leantime,

if(Leantime < =8,'Ontime','Late') as Status.

From xyz.

Now you can use the field Status in your bar chart.

2. if you want charts to start from 50% then you need to fo in properties -> Axes -> Here you need to define the minimum and maximum scale. Also need to uncheck Forced 0 for all the dimensions.

Hope you understood.

Re: creating charts like Excel

Anonymous — Mon, 22 Aug 2011 13:15:03 GMT

Hi Kaushik,

I understood it. Thanks!! However, since the field LeanTime does not exists in Excel, Qlikview gives me an error message when doing a reload.

Interval(Date(Date#(POD, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY hh:mm') - date(Date#(DEP, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY hh:mm'), 'd hh:mm') as LeanTime,

if(LeanTime<=8,'OnTime','Late') as Status (Does Not Work)

Why can't I "link" it to the above expression?

How Can I tackle this issue?

Thank you very much for you help!!!

Regards,

iSam

creating charts like Excel

kaushiknsolanki — Mon, 22 Aug 2011 18:17:02 GMT

Hi,

In that case you should use in this way.

if(

Interval(Date(Date#(POD, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY hh:mm') - date(Date#(DEP, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY hh:mm'), 'd hh:mm') <= 8,'ON TIME','LATE') as Status.

Regards,

Kaushik Solanki

creating charts like Excel

Anonymous — Tue, 23 Aug 2011 10:25:57 GMT

Hi Kaushik,

Sorry for the late response! Thanks again for your help!! The formula works!!

Now I "just" have to figure out how to convert the numbers of shipments to percentage.

I went to number tab and selected "show in percent" however this not give me the result I want, because the maximum percent should not be more than 100%. :S

What is it that im doing wrong?

iSam

creating charts like Excel

kaushiknsolanki — Tue, 23 Aug 2011 11:06:56 GMT

HI,

Its because you havent done any calculation for percentage.

Let me explain you with example.

Say you have 200 calls and from that 70 calls are Ontime and 130 are Late.

Then when i say in percentage. i.e percentage of the Late calls will ve (130*100)/200 = 75%

So in your case you need to find the total Calls and then devide that.

In Qlikview your expression will be count(DEP) / count (Total DEP)

Regards,

Kaushik Solanki

Re: creating charts like Excel

Anonymous — Tue, 23 Aug 2011 11:47:19 GMT

Hi Kaushik,

Thanks again for your help!! I really appreciate it!!

It’s a pity that there is no book that cover these aspects. L

Regards,

iSam

creating charts like Excel

kaushiknsolanki — Tue, 23 Aug 2011 12:25:16 GMT

Hi,

Dont worry experience will teach you all these things.

Cheers.

Regards,

Kaushik Solanki

Re: creating charts like Excel

Anonymous — Mon, 05 Sep 2011 06:55:03 GMT

Hi Kaushik,

I wasn’t able to add another if statement therefore I’ve splitted the script into 2 formulas:

if(Interval(Date(Date#(POD, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY hh:mm') - date(Date#(DEP, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY hh:mm'), 'd hh:mm') <= 8,'ON TIME','LATE') as Status.

if(Interval(Date(Date#(POD, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY hh:mm') - date(Date#(DEP, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY hh:mm'), 'd hh:mm') <= 7,'ON TIME','LATE') as StatusII.

How can I combine both “rules” to 1 “rule”? Suppose I want to state that shipments to destination “JFK” have a maximum of 8 days and shipments to “DBX” have a maximum of 7 days?

I want to do this so I don’t have to create a field for each specific ‘requirement’.

Hope you can help

Regards

iSam

Re: creating charts like Excel

Mon, 05 Sep 2011 08:01:44 GMT

Ich werde ab 05.09.2011 nicht im Büro sein. Ich kehre zurück am

23.09.2011.

Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Verwertung des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt.

Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

creating charts like Excel

kaushiknsolanki — Mon, 05 Sep 2011 09:31:40 GMT

Hi,

Try this way.

If(Field= 'JFK',

if(Interval(Date(Date#(POD, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY hh:mm') - date(Date#(DEP, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY hh:mm'), 'd hh:mm') <= 8,'ON TIME','LATE'),

if(Field = 'DBX',

if(Interval(Date(Date#(POD, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY hh:mm') - date(Date#(DEP, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY hh:mm'), 'd hh:mm') <= 7,'ON TIME','LATE'))) as Status.

Regards,

Kaushik Solanki

Re: creating charts like Excel

Anonymous — Mon, 05 Sep 2011 12:56:06 GMT

Hi There,

Thanks a lot for your help again!!! I really appreciate it.

It works, I can reload my script without error. However the results on my chart are different now: This is the ‘old’ status field (I renamed it ‘status2’) is different than the ‘status’ field.

if(Interval(Date(Date#(POD, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY') - Date(Date#(DEP, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY'), 'D')<=7, 'OnTime', 'Late') as Status2,

The Status is script is now:

if(Re: creating charts like Excel='BJS',

if(Interval(Date(Date#(POD, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY') - Date(Date#(DEP, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY'), 'D')<= 8, 'OnTime','Late'),

if(Re: creating charts like Excel='IZM' OR 'AMS',

if(Interval(Date(Date#(POD, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY') - Date(Date#(DEP, 'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY'), 'D')<=7, 'OnTime', 'Late'))) as Status,

It seems that the field “Status” has three ‘options’ ‘OnTime’, ‘Late’ and ‘-‘ I just don’t know where ‘-‘ comes from. :S

Re: creating charts like Excel

Mon, 05 Sep 2011 14:02:47 GMT

Ich werde ab 05.09.2011 nicht im Büro sein. Ich kehre zurück am

23.09.2011.

Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

Re: creating charts like Excel

Mon, 05 Sep 2011 14:02:51 GMT

Ich werde ab 05.09.2011 nicht im Büro sein. Ich kehre zurück am

23.09.2011.

Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

creating charts like Excel

kaushiknsolanki — Mon, 05 Sep 2011 15:33:30 GMT

Hi,

When ever you use if statment, and if the field using in condition have more values then whct you have used, then you will fine - i.e NULL.

So in your case "Creating chart like excel" field will have more values then just BJS, AMS and IZM.

So when every you use this field in chart as dimension then dont forget to click "suppress when NULL"

Regards,

Kaushik Solanki

Re: creating charts like Excel

Mon, 05 Sep 2011 20:03:14 GMT

Ich werde ab 05.09.2011 nicht im Büro sein. Ich kehre zurück am

23.09.2011.

Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

Re: creating charts like Excel

Anonymous — Wed, 07 Sep 2011 06:16:23 GMT

Hi Thanks a lot!!!! I found the problem. It wasn’t because of an extra field, it was because of the miss used “OR” statement. I did …if(destination=”IZM” OR “AMS”,….

Again Thanks for all your help, much appreciated!!!