topic Re: Bar chart display in QlikView

Bar chart display

konidena — Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

I have column called CAL_Period. The data in the column is of the of format 'YYYYMM'

The starting value is 199305, End value is 201612.

Question:

I need to show 4 bars in bar chart. 1st bar current year, 2nd for current year -1, 3rd for current year-2, 4th for current year -3. These bars should display from right to left.

That is , 2013, 2014, 2015, 2016

for 2013 bar, i need to show the sum of my measure starting from 199305 to 201312

for 2014 bar, i need to show from 199305 to 201412

for 2015 bar, i need to show from 199305 to 201512

for 2016 bar, i need to show from 199305 to 201612

How to implement this.

Regards

Srinivas

Re: Bar chart display

sunny_talwar — Fri, 30 Dec 2016 12:39:13 GMT

Have you read these thread my friend?

QlikCommunity Tip: How to get answers to your post?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

We are all trying to help each other, but you need to give us enough information so that we can help you as quickly as possible

Re: Bar chart display

ganeshsvm — Fri, 30 Dec 2016 13:23:27 GMT

Hi,

You should have year field in your data, use that year field as your dimension and select Full accumulation in Expression tab.

If your calendar is properly mapped, You will get your desired results,

Regards,

Ganesh

Re: Bar chart display

konidena — Fri, 30 Dec 2016 13:27:20 GMT

Attached the sample data

Regards

Srinivas

Re: Bar chart display

konidena — Fri, 30 Dec 2016 13:32:33 GMT

I have cal_Year column. This column i am using as dimension.

I have issue in creating the expression.

Regards

Srinivas

Re: Bar chart display

giakoum — Fri, 30 Dec 2016 13:58:06 GMT

the easy solution :

create 4 expressions as follows :

Sum({<Cal_Period={"<=201312"}>} Sales)

Sum({<Cal_Period={"<=201412"}>} Sales)

Sum({<Cal_Period={"<=201512"}>} Sales)

Sum({<Cal_Period={"<=201612"}>} Sales)

Re: Bar chart display

sunny_talwar — Fri, 30 Dec 2016 14:09:44 GMT

May be this:

=RangeSum(Above(Sum({<Cal_Period>}Sales), 0, RowNo())) * Avg({<Cal_Period = {"$(='>=' & Date(AddMonths(Max(Cal_Period), -3), 'YYYYMM') & '<=' & Date(AddMonths(Max(Cal_Period), 0), 'YYYYMM'))"}>}1)

Re: Bar chart display

konidena — Fri, 30 Dec 2016 14:11:09 GMT

You are right and it should bring the values as expected.

But, when i take CAL_Year as my dimension, it is showing the bars for 2011, 2012 also,

I need to show only 2013, 2014,2015 and 2016 values related bars.

Is there any other way to restrict the year based on the expression?

Regards

Srinivas

Re: Bar chart display

sunny_talwar — Fri, 30 Dec 2016 14:17:34 GMT

For year, you can try this:

=RangeSum(Above(Sum({<Cal_Period, Cal_Year>}Sales), 0, RowNo())) * Avg({<Cal_Year = {"$(='>=' & (Max(Cal_Year)-3) & '<=' & Max(Cal_Year))"}>}1)

Re: Bar chart display

giakoum — Fri, 30 Dec 2016 14:19:28 GMT

there is no need for dimension when you have those expressions

Re: Bar chart display

giakoum — Fri, 30 Dec 2016 14:20:43 GMT

and here is a complete, no hard coded years solution

Re: Bar chart display

ganeshsvm — Mon, 02 Jan 2017 10:16:53 GMT

Alternate solution without using complicated expression and thru backend script is,

LOAD *,
if(Left(Cal_Period,4)<=2013, 2013, Left(Cal_Period,4)) as RYear,
Cal_Period,
Sales
FROM

(ooxml, embedded labels, table is Sheet1);

Then use this RYear in your bar chart as dimension, and use sum(sales) as expression and check the Full Accumulation option in Expression tab.

Regard,

Ganesh