topic Re: help with a chart in QlikView

help with a chart

Thu, 19 Feb 2015 09:45:49 GMT

Hi,

Please see attached app. You will the screenshot of the chart we would like to replicate. It's a chart that shows both monthly and quarterly performance (quarterly scores are the bars on the chart, the monthly data is on the line).

We have brought through raw data too.

Be amazing if this could be replicated the chart on this app in Qlikview..?

I have tried to create a chart but wasnt working. To avoid confusion I have removed it.

Chris

Re: help with a chart

prma7799 — Thu, 19 Feb 2015 09:49:25 GMT

What is your requirement?

Re: help with a chart

manojkulkarni — Thu, 19 Feb 2015 10:01:59 GMT

We can replicate the image not exactly same, but near to that look. Send the sample data, will try & post it back

Re: help with a chart

rubenmarin — Thu, 19 Feb 2015 10:31:56 GMT

Hi Chris, please check attachment, I added a chart to give you an idea.

You'll need to relate months to a number so you can sort them, if you want the Y axis to start at 5 you can set that in Axis tab.

The Y axis grid lines on top of bars can be set through reference lines (in presentation tab)

The legend can be set through 'Text in Graph' or with text objects outside the graph object.

Colors can be changed through bgcolor of the expressions.

Separtion between bars is setted in presentation tab.

Re: help with a chart

morganaaron — Thu, 19 Feb 2015 10:33:16 GMT

Hi Chris,

Rough idea here. You need to look at a few things:

> Formatting your fields into readable date variables so Qlik knows how to sort them.

> Creating a conjoined Quarter/Month field so you get a consistent line rather than "groupings".

Unsure of why you were highlighting a particular period or the numbers after month names so left those bits out. Have a look through the app (especially the script) to get an idea of why I did certain things! !

Re: help with a chart

Thu, 19 Feb 2015 15:56:16 GMT

Hi Ruben,

This is amazing. Copied it to another app but says no data to display. Can you explain what has been changed to get this to work? Can't see anything signifcant on the script or chart tab.

Chris

Re: help with a chart

rubenmarin — Thu, 19 Feb 2015 16:01:25 GMT

Hi Chris, can you upload a sample of that app?

It can be because in my sample there was only one value for each month, if you have more than value you'll need to set the field in an agregation function that returns one value for month, in example:

Change:

[PI2. Overall sat with service1]

To:

Avg([PI2. Overall sat with service1])

Re: help with a chart

morganaaron — Thu, 19 Feb 2015 16:04:12 GMT

Just to check though Chris, the sort order for this won't be correct as Qlik isn't associating your fields with fields it knows how to sort. Where as a temporary solution looks good, you should probably look to address the underlying issues to make sure you don't encounter similar issues going forward!

Re: help with a chart

Thu, 19 Feb 2015 17:00:11 GMT

Hi Aaron, Ruben.

Thank you.

two other questions:

1) Ruben you mention "Colors can be changed through bgcolor of the expressions" - can you explain how this is done? Looking in the properties of the chart

2) Aaaron - you mention "Creating a conjoined Quarter/Month field so you get a consistent line rather than "groupings". So in the raw data introducing a column called Quarter/Month with data such as 'Q4 - Jan' would work?

Chris

Re: help with a chart

rubenmarin — Fri, 20 Feb 2015 08:09:21 GMT

Hi Chris, expressions can be expanded to change some attributes, in background color you can set a condition to assign a color to the bars, you can assign one of the preset colors (like "Red()") or use a funtion to assign a especific color (using RGB(), ARGB()...)

Re: help with a chart

Tue, 24 Feb 2015 12:25:14 GMT

Hi Ruben,

Yes - thanks for rejigging memory over the colours.

I do have another question - done reply to original post.

Chris

Re: help with a chart

Tue, 24 Feb 2015 12:29:13 GMT

Hi all,

Thanks for assistance to date.

Tried to take on board comments and created a conjoined 'quarter-month' field in the data source; however, chart is still not seperating 'month' form the 'quarter' scores - its showing the same values.

Can you help?

Chris

Re: help with a chart

rubenmarin — Tue, 24 Feb 2015 12:37:31 GMT

Hi Chris, I see that [PI2. Overall sat with service] has the same data values for each month in the same quarter, and seems that this data is loaded from xls.

Re: help with a chart

Tue, 24 Feb 2015 14:06:35 GMT

Hi Ruben,

Sorry please see figures updated. The values change each month; however, will be the same for the respective (i.e the quarter score is the average of the 3 months individual values). I have updated table in app to show as a pivot table which then identifies how the values work and what we are trying to show.

The quarter block needs to be same for 3 months with the calcualtion being avg of Q1, avg of Q2 etc.

I realise I have amde this error when raising the question.. thanks for picking up.

Chris

Re: help with a chart

rubenmarin — Tue, 24 Feb 2015 15:05:54 GMT

Hi Chris, I'm a bit busy, a quick solution can be use this expression:

If(Quarter='Q1', Avg(TOTAL {<Quarter={'Q1'}>} [PI2. Overall sat with service]),

If(Quarter='Q2', Avg(TOTAL {<Quarter={'Q2'}>} [PI2. Overall sat with service]),

If(Quarter='Q3', Avg(TOTAL {<Quarter={'Q3'}>} [PI2. Overall sat with service]),

If(Quarter='Q4', Avg(TOTAL {<Quarter={'Q4'}>} [PI2. Overall sat with service])))))