Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
aboumejjane
Contributor

creating charts like Excel

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

1 Solution

Accepted Solutions

creating charts like Excel

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

16 Replies

creating charts like Excel

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.

aboumejjane
Contributor

Re: creating charts like Excel

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

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

aboumejjane
Contributor

creating charts like Excel

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.

snapshot.JPG

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%. Smiley Frustrated

snapshot_II.JPG

What is it that im doing wrong?

iSam

creating charts like Excel

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

aboumejjane
Contributor

Re: creating charts like Excel

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

Hi,

    Dont worry experience will teach you all these things.

    Cheers.

Regards,

Kaushik Solanki

aboumejjane
Contributor

Re: creating charts like Excel

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

Not applicable

Re: creating charts like Excel

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!

Community Browser