topic Re: Comparing two years data in bar chart showing minus and plus values in QlikView

Comparing two years data in bar chart showing minus and plus values

Tue, 29 Jul 2014 12:00:16 GMT

Hello,

I want to create bar chart which will compare two years and show if 2013 was + or - in comparison with 2012.

It should look like this:

The expression of the right (green) chart is Count(VIN) - because we are counting VIN codes of cars in excel file, and dimension is Segment, or Category and Year.

I am unable to create the right expression to compare two years, in excel it should be like ((2013/2012)*100)-100

Here are few lines from the Excel source file... (Rok is Year, Mesiac is Month)

http://www.zapsr.sk/wp-content/uploads/2014/07/Two-year-comparison.xlsx

Thank you for all suggestions.

Re: Comparing two year´s data in bar chart showing minus and plus values

Tue, 29 Jul 2014 12:04:55 GMT

* please do not post *.qvw files - I didnt purchase Qlickview yet... so I am not able to view others apps.

Thank you.

Re: Comparing two years data in bar chart showing minus and plus values

c_gilbert — Tue, 29 Jul 2014 14:42:47 GMT

- Create a bar chart

- Set the orientation to horizontal

- Create expressions to calculate the figures for the 2 years you would like to compare (but don't show these on the chart)

(I would create variables for these to include in the expressions)

- Create an expression to calculate the variance % (and show this on the chart)

- Check show values on data points

- Set a conditional background colour so that positive values are green, and negative values are red.

Re: Comparing two years data in bar chart showing minus and plus values

Wed, 30 Jul 2014 06:18:57 GMT

OK, i created

Count({<[Year]={'2012'}>} [VIN]) - named "2012"

and

Count({<[Year]={'2013'}>} [VIN]) - named "2013"

But I am not sure how to calculate the variance % ...i just wrote the expression as "=2013/2012" and then tried "=((2013/2012)*100)-100 but nothing of these gave me data to show

To the variables - I am a real beginner an I am not sure what they (variables) are supposed to do (but I will find It somewhere here and learn) ...so If its not necessary to use variables, I wouldn't for now.

Re: Comparing two years data in bar chart showing minus and plus values

c_gilbert — Wed, 30 Jul 2014 07:58:04 GMT

I wouldn't worry about the variables for now then.

I would first create an expression that will just show the variance:

= [2013] - [2012] - named "Variance"

Then you can reference this expression in another expression:

= [Variance] / [2012]

Make sure only the Variance expression is shown as a bar chart, and for the rest, just untick any display options (but don't disable the expression)

Next, make sure the number format of this expression is set to show as %.

Re: Comparing two years data in bar chart showing minus and plus values

Wed, 30 Jul 2014 08:25:16 GMT

I still have no data to display.

My expressions are as follows. Dimensions are Category and Year

Number is set to Number and show in % 😕

Re: Comparing two years data in bar chart showing minus and plus values

c_gilbert — Wed, 30 Jul 2014 08:29:56 GMT

It's probably because you have disabled all your expressions!

Check 'enable' on [2012], [2013] and [Variance], but then in the 'Display Options' section, untick any options there (e.g. bar / symbol / line) so they don't display on the chart.

Also... I'm still learning myself, but I think you need an extra symbol in your set analysis, i.e.:

Count ( {$<[Year] = {'2013'} >} [VIN] )

Re: Comparing two years data in bar chart showing minus and plus values

Wed, 30 Jul 2014 09:08:09 GMT

Yes, that was the problem... Thank you!

Actually It is working also without the extra symbol