Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

16 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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.

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anonymous
Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anonymous
Not applicable
Author

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%. :S

snapshot_II.JPG

What is it that im doing wrong?

iSam

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anonymous
Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

    Dont worry experience will teach you all these things.

    Cheers.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anonymous
Not applicable
Author

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
Author

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!