topic Re: Trying to create a chart in QV - help! in QlikView

Trying to create a chart in QV - help!

Fri, 01 Jul 2011 20:48:50 GMT

I have created tried to create chart in QV to show budget and schedule variance over time. Also, I've tried to add a control to this time to show the information by day, week and month. I also have to show the variance by time with a specific project manager - I haven't done this yet, but I'm assuming I'll have to put it on another sheet?

But with regards to the below chart - have I done the task I set out to do or show? I think I have, but it's my first day on QV and I have no idea! Any help would be so much appreciated.Thank you!

Also, to show the variance for each project as of today can I do it on this or create another chart/sheet for that? I have attached the details of the task. Thanks.

Re: Trying to create a chart in QV - help!

johnw — Fri, 01 Jul 2011 22:44:46 GMT

Looks pretty good so far.

"Add a control to the time axis allowing the user to show this information by day, week or month."

This sounds like a request for something that I typically add to charts like this - a cyclic group as the dimension. Do this:

settings -> document properties -> groups -> new -> Group Name = Period -> checkmark Cyclic Group -> add Month, Week and Day -> OK -> OK

Now in your chart, remove the Month dimension and add the Period dimension. The default view will be by month, but by clicking on the little circular arrow, you can cycle between the Month, Week and Day.

Re: Trying to create a chart in QV - help!

Fri, 01 Jul 2011 23:44:48 GMT

John, I did that and it worked brilliantly! Thank you so much! The cyclic

thing worked well. I noticed that my schedule variance is kinda flat

lining...how would I change that?

On Fri, Jul 1, 2011 at 3:44 PM, John Witherspoon <

Re: Trying to create a chart in QV - help!

johnw — Fri, 01 Jul 2011 23:51:43 GMT

I'm not sure what you mean by "flat lining".

Re: Trying to create a chart in QV - help!

Fri, 01 Jul 2011 23:53:48 GMT

Oh, I just meant that's it just keep showing the line as flat or horizontal

- thus not giving any information about the schedule variance. I'm assuming

it's something to do with changing the axis numbers? But I'm not sure how to

do this.

Trying to create a chart in QV - help!

johnw — Fri, 01 Jul 2011 23:56:56 GMT

I have no idea why the line would be flat/horizontal. Can you post the application?

Re: Trying to create a chart in QV - help!

Sat, 02 Jul 2011 00:11:50 GMT

I attached it. Let me know if it worked. Thanks

Re: Trying to create a chart in QV - help!

johnw — Sat, 02 Jul 2011 00:25:11 GMT

Well, the sum(CostBudgetVariance) line seems to vary plenty. So it's the sum(MoveInStatusDays) that is the problem? If so it's because it has a drastically different scale than the other. The way to display them both in a single chart is to give the second expression its own scale:

chart properties -> axes -> select sum(MoveInStatusDays) -> Position = right(Top) -> OK

Then they'll both have displayable values.

The next problem I see is that you can't see one line because the other is on top of it. I recommend using transparent colors for that.

chart properties -> colors -> click on the first color -> select "calculated" -> type argb(130,0,0,130) in the box -> OK -> click on the second color -> select calculated -> type argb(130,130,0,0) in the box -> OK -> OK

I think that makes it easier to see crossing and overlapping lines.

Ah, I see another problem. I really wouldn't define Month and Week and Day the way you have. I'd define them like this:

,date(monthstart(SnapshotDate),'MMM YYYY') as Month
,date(weekstart(SnapshotDate)) as Week
,date(SnapshotDate) as Date

The problem with what you have now is that multiple years, months and weeks will all sort of intermingle in your chart. Day 15 of every month will just show up as 15. That's probably not what you want. The code above will make sure that everything is in the right sequence and not combined on your axis.

Re: Trying to create a chart in QV - help!

Sat, 02 Jul 2011 00:46:41 GMT

I made all the changes you suggested and it looks a lot better - esp the

axis and the variance lines. Thanks so much! I never would have been able to

figure it out. I just realised my instructions also tell me to look at

variance for each project as of today - do you know what would mean?

On Fri, Jul 1, 2011 at 5:25 PM, John Witherspoon <

Re: Trying to create a chart in QV - help!

johnw — Sat, 02 Jul 2011 00:54:22 GMT

Maybe some chart that specifically just always shows today's variance? So assuming that today is one of the days in your data set, just show the variance for that day? Like this?

Dimension = project

Expression = sum(if(Date=today(),CostBudgetVariance))

Or the higher-performance alternative (look up "Set Analysis" in the help text index if you REALLY want to dive into the deep end of QlikView):

sum({<Date={'$(=today())'}>} CostBudgetVariance)

That's assuming you define the date like I mentioned, so that it's actually a date, so that it can be compared to today(). Hmmm, a particularly low performance version would be like this:

Dimension 1 = project

Dimension 2 = if(Date=today(),Date)

Expression = sum(CostBudgetVariance)

OK, seriously, I'm going home now.

Re: Trying to create a chart in QV - help!

Sat, 02 Jul 2011 01:00:30 GMT

Wow! Thanks again. I'll definitely try your suggestions. Thank you so much.

Have a great weekend!

On Fri, Jul 1, 2011 at 5:54 PM, John Witherspoon <

Re: Trying to create a chart in QV - help!

Tue, 05 Jul 2011 20:53:54 GMT

John,

Sorry to bother you, but I'm really stuck on something in QV and I just want

to run a few things by you. I'm on the last few steps of the project and I

really just want to get it done

The task wants me to show the volume for change orders by project manager

and the purchase orders by vendor. Is it possible to put this on the same

chart? Also I have to show grouping by managing office and vendor.

I don't think the expressions I have - count(changeorder_num) and

count(purchaseorder_num) does this? Plus I have to group by managing office

and vendor. So far I have made project manager a dimension and the

count(changeorder_num) and count(purchaseorder_num) my expressions. To show

grouping by managing office and vendor I used the group feature - the cyclic

one...is this correct? I've attached a screen shot of what I have so far.

I would appreciate any help as I have this task to complete as part of an

interview process and I have never used QV before. Just learning as I go!

Any help would be fantastic. Thank you!!

>> QlikCommunity <http://community.qlik.com/index.jspa>

>> Re: Trying to create a chart in QV - help! created by John Witherspoon<http://community.qlik.com/people/johnw>in

>> New to QlikView - View the full discussion<http://community.qlik.com/message/129515#129515>

Re: Trying to create a chart in QV - help!

johnw — Tue, 05 Jul 2011 21:23:42 GMT

It sounds to me like you want two different charts. One for the volume for change orders by managing office and project manager, and one for the purchase orders by vendor. I'm unclear how these charts would be related, so I'm guessing they aren't. I could be completely wrong, of course, but it sounds like this:

Chart 1:
dimension 1 = managing office
dimension 2 = project manager
expression = count(distinct changeorder_num)

Chart 2:
dimension = vendor
expression = count(distinct purchaseorder_num)

Best to probably have both charts visible at once, but if that isn't practical, you could set both charts to auto minimize when the other is opened: properties -> caption -> allow minimize -> auto minimize.

I put in "distinct" in case one change order or purchase order can appear more than once. It may not be needed in practice.

Re: Trying to create a chart in QV - help!

Tue, 05 Jul 2011 21:26:21 GMT

Thanks so much John. I'll try that and let you know how it goes. Much

appreciated.

QlikCommunity <http://community.qlik.com/index.jspa>
Re: Trying to create a chart in QV - help! created by John Witherspoon<http://community.qlik.com/people/johnw>in
New to QlikView - View the full discussion<http://community.qlik.com/message/130298#130298>

Trying to create a chart in QV - help!

Tue, 05 Jul 2011 21:40:18 GMT

Hi John,

I made the changes that you suggested and it looks a lot simpler and easier to read the data. Thanks. I'll have to see what they say about it. Anyways, I've also started making a Gantt Chart and I'm having some problems actually seeing the data. I looked this up and it seems that it doesn't work with the 64 bit QV - is this true?

Trying to create a chart in QV - help!

johnw — Tue, 05 Jul 2011 23:25:44 GMT

I'm afraid I haven't done any Gantt charts, and don't know if there's a problem with them in 64-bit QlikView.

Re: Trying to create a chart in QV - help!

Wed, 06 Jul 2011 00:05:35 GMT

No problem John. Thanks for all your help thus far.