topic Re: Combo chart help in QlikView

Combo chart help

Anonymous — Wed, 22 Oct 2014 17:48:01 GMT

Hello,

Newer to Qlikview and I am looking for some help creating a combo chart. I have been able to set up the line portion of the chart, which is a sum of sales per period. Period is set up in the form 201301, 201302, etc, and covers 2 years. I am having trouble setting up the bar graph part of the chart, where I am trying to show the change between months, while also trying to take care of change between 201401 and 201312.

Here are my fields -

Sales

Period

States

Bar Graph should look like:

Sales 201302-201301, Sales 201303-201302, and etc

Thank you,

Justin

Re: Combo chart help

JonnyPoole — Wed, 22 Oct 2014 18:56:38 GMT

If i understandy you correctly , your lines use an expression similar to this: sum(Sales)

If that is the case, try using the following expression for your bars: sum(Sales) - Above( sum(Sales),0,1)

the above() function grabs previous dimension values . In this case 0 is the offset and 1 is the number of preceding records to grab. It should give you this month - last month

Re: Combo chart help

Anonymous — Wed, 22 Oct 2014 19:22:26 GMT

Jonathan,

That did not seem to work. In the excel file that I loaded into Qlikview, the data is set up with multiple sales figures by states, in each month. I typed the above as is, and it did not bring in anything. Also, this did not work when I tried it in an input box.

Any other thoughts?

Re: Combo chart help

JonnyPoole — Wed, 22 Oct 2014 19:33:34 GMT

Ok. Then i will need more information about your app. Can you share your QVW ? screenshot of your line chart so far ?

Re: Combo chart help

Anonymous — Wed, 22 Oct 2014 19:50:52 GMT

Here is a copy of some of my data

Year	Month	Risk state	Sales
2014	Jan	AL	842
2014	Jan	AL	11281
2014	Jan	AL	13717
2014	Jan	AL	0
2014	Jan	AL	612
2014	Jan	AL	11564
2014	Jan	AL	0
2014	Jan	AL	0
2014	Jan	AL	0
2014	Jan	AL	0
2014	Jan	AL	710
2014	Jan	AK	0
2014	Jan	AK	198
2014	Jan	AK	0
2014	Jan	AK	0
2014	Jan	AK	0
2014	Jan	AK	0
2014	Jan	AK	0
2014	Jan	AK	0
2014	Jan	AK	0
2014	Jan	AK	0
2014	Jan	AK	13
2014	Jan	AZ	1016
2014	Jan	AZ	16536
2014	Jan	AZ	15731
2014	Jan	AZ	0
2014	Jan	AZ	829
2014	Jan	AZ	755
2014	Jan	AZ	0
2014	Jan	AZ	0
2014	Jan	AZ	0
2014	Jan	AZ	0
2014	Jan	AZ	1056
2014	Jan	AR	242
2014	Jan	AR	5983
2014	Jan	AR	5471

Re: Combo chart help

Anonymous — Wed, 22 Oct 2014 19:55:56 GMT

Re: Combo chart help

Anonymous — Wed, 22 Oct 2014 19:56:34 GMT

And there is my corresponding graph using the code. It does not seem to be loading in anything to the sales change line.

Re: Re: Combo chart help

JonnyPoole — Wed, 22 Oct 2014 20:41:54 GMT

hmm... i think i overcomplicated it

switch it to :

sum(Sales)- above( sum(Sales))

for the first row, there is no difference so add a check to 0 out the first value for difference

sum(Sales)- if( isnull(above( sum(Sales))), sum(Sales),above(sum(Sales)))

If you need a breakdown by State, we can do that too, its a very similar check to ensure the State on the above row is the same as the current row

Re: Combo chart help

Anonymous — Thu, 23 Oct 2014 14:09:18 GMT

Jonathan,

Thank you for the help, that worked. Can you explain the syntax so I can understand better for further use.

Thanks again!!

Re: Combo chart help

JonnyPoole — Thu, 23 Oct 2014 14:17:08 GMT

Great.

The above() function is handy in this because it allows you to grab the value for the previous dimension value. Imagine your chart in tabular/text format. above() is grabbing the value that literally comes before. You can grab the row from 1 row above or multiple rows , even sum up multiple rows. there are other functions like this like before() , previous(), top(), bottom() etc...

Now when you are calculating the FIRST dimension value, there is no value before it and above() returns NULL. In that case i use isnull() to check for that situation and calculate differently.

Does that help ?

Re: Combo chart help

Anonymous — Thu, 23 Oct 2014 14:45:40 GMT

Jonathan,

That does help to understand the Above function. How would that differ from either previous, before, or top?

Re: Re: Combo chart help

JonnyPoole — Thu, 23 Oct 2014 14:54:40 GMT

Here is a snippet from the help that covers this family of functions. They are called 'Chart Inter-Record Functions' . They allow you to grab values from other rows in the chart/table.

Its also accessible by going to the help meny

help->contents -> index -> type: 'chart inter record functions'