creating charts like Excel

[Anonymous]

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

[kaushiknsolanki]

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.

[Anonymous]

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

[kaushiknsolanki · Accepted solution]

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

[Anonymous]

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

[kaushiknsolanki]

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

[Anonymous]

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

[kaushiknsolanki]

Hi,

    Dont worry experience will teach you all these things.

    Cheers.

Regards,

Kaushik Solanki

[Anonymous]

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

[#10]

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!