topic Re: Find the difference in value between bars in bar chart in QlikView

Find the difference in value between bars in bar chart

udaya_kumar — Thu, 16 Jun 2016 14:10:18 GMT

Hi,

I have a bar chart, consider the attached image.

I want to find the difference in the values between the bars in bar chart and show that value as a line chart in combo chart.

For example:

ID 1, Value 10

ID 2, Value 24

So the difference between them is 24-10=14

then

ID 3, Value 30

So the difference between them is 30-24=6

Like this, i want to show the difference value between them as a line or trend in the combo chart.

How can we achieve this?

Re: Find the difference in value between bars in bar chart

sunny_talwar — Thu, 16 Jun 2016 14:12:03 GMT

May be like this (assuming your current expression is Sum(Sales))

Sum(Sales) - Above(Sum(Sales))

Re: Find the difference in value between bars in bar chart

udaya_kumar — Thu, 16 Jun 2016 14:17:54 GMT

Hi Sunny,

Thank you for the reply.

I am having two expressions in the bar chart and i have to find the difference between them.

PFA the snapshot of the bar chart with sample.

Re: Find the difference in value between bars in bar chart

sunny_talwar — Thu, 16 Jun 2016 14:20:14 GMT

Earlier screenshot only showed one expression. Would you be able to provide a sample with the expected output? For a sample, qvw would be helpful to play around with

Re: Find the difference in value between bars in bar chart

udaya_kumar — Thu, 16 Jun 2016 14:27:12 GMT

PFA the sample qvw.

It shows two expressions, i want the difference between two bar values and show that as a trend in the same chart.

Re: Find the difference in value between bars in bar chart

antoniotiman — Thu, 16 Jun 2016 14:31:34 GMT

Like this

(Alt(Sum(Value),0)+Alt(Sum(Value1),0))-Above(Alt(Sum(Value),0)+Alt(Sum(Value1),0))

Re: Find the difference in value between bars in bar chart

sunny_talwar — Thu, 16 Jun 2016 14:35:15 GMT

Like this?

Expression:

If(Sum(Value) > 0, Sum(Value)-Above(Sum(Value)),

If(Above(sum(Value1)) = 0, Sum(Value1)-Above(Sum(Value)), Sum(Value1)-Above(sum(Value1))))

Re: Find the difference in value between bars in bar chart

udaya_kumar — Thu, 16 Jun 2016 14:52:01 GMT

Hi,

Sorry for that sample file.

I thought the logic which you have given will work in my actual file, but it did not work.

PFA the file which has the data similar to actual file.

Using this qvw, i want the same functionality.

The data has one bar for current data and other bars are for history data.

Please check this file and help me to get the difference between the bar values and show them as a trend.

Re: Find the difference in value between bars in bar chart

udaya_kumar — Thu, 16 Jun 2016 14:53:27 GMT

Sample excel file data used.

Re: Find the difference in value between bars in bar chart

sunny_talwar — Thu, 16 Jun 2016 14:56:18 GMT

This?

Expression:

If(sum({<Flag={0}>} Value) > 0, sum({<Flag={0}>} Value)-Above(sum({<Flag={0}>} Value)),

If(Above(sum({<Flag={1}>} Value)) = 0, sum({<Flag={1}>} Value)-Above(sum({<Flag={0}>} Value)), sum({<Flag={1}>} Value)-Above(sum({<Flag={1}>} Value))))

Re: Find the difference in value between bars in bar chart

antoniotiman — Thu, 16 Jun 2016 15:05:19 GMT

(Alt(sum({<Flag={0}>} Value),0)+Alt(sum({<Flag={1}>} Value),0))-Above(Alt(sum({<Flag={0}>} Value),0)+Alt(sum({<Flag={1}>} Value),0))

Re: Find the difference in value between bars in bar chart

udaya_kumar — Thu, 16 Jun 2016 15:14:04 GMT

Yes, almost we have reached at my requirement.

Sometimes i get multiple bars for same date, that time it is not working.

Like shown in the attached image,

Re: Find the difference in value between bars in bar chart

sunny_talwar — Thu, 16 Jun 2016 15:15:32 GMT

What would the trend line look like when this is the case?

Re: Find the difference in value between bars in bar chart

udaya_kumar — Thu, 16 Jun 2016 15:23:09 GMT

It is coming like this way in the chart.

Please check the attached image.

I want the difference value here like

267-592=-325 (Bar 1 and 2)

592-267=325 (Bar 2 and 3)

207-592=-385 (Bar 3 and 4)

465-207=258 (Bar 4 and 5)

910-465=445 (Bar 5 and 6)

And these values -325,325,-385,258,445 in the trend lines in the same chart.

Re: Find the difference in value between bars in bar chart

Sat, 18 Jun 2016 09:17:33 GMT

Hi Uday,

Attaching 2 files:

1> App1.qvw has similar chart developed as per your "Bar Chart1.png"

2> App2.qvw has bar chart developed based on "Test.xlsx".

I have calculated Difference Line values in the script and shown against max ID for each month.

Hope this suffices your requirement as I assumed calculation logic is based on the "Bar Chart1.png".

Re: Find the difference in value between bars in bar chart

udaya_kumar — Thu, 28 Jul 2016 08:49:06 GMT

Hi parag,

Thanks for the reply.

I want the chart similar to the one attached.

I want the difference in values between each bars. If you consider the first set of bars, values of bars are like 10,24,30,40,50. So i want the difference in values between them like 24-10 = 14, 30-24 = 6, 40-30 = 10, 50-40 = 10.... I want to shows these values at least or show these values with the lines in combo chart.