topic Re: Re: How to create a candlestick chart??? in QlikView

How to create a candlestick chart???

Mon, 18 Aug 2014 14:55:14 GMT

I am a starter to use qlikview, plz give a suggestion to me in order to make a candlestick chart.

I have such excel file

TIME	LAST
09:14:29.528551	22951
09:15:29.614293	22961
09:15:29.639119	22968
09:15:29.641101	22951
09:15:29.878346	22975
09:15:29.882078	22978
09:15:29.884098	22979
09:15:30.007220	22965
09:15:30.024216	22980
09:15:30.026978	22979
09:15:30.105209	22980
09:15:30.236066	22967
09:15:30.322187	22978
09:15:30.372227	22980
09:15:30.512260	22983
09:15:30.742066	22970
09:15:30.744072	22963
09:15:30.959001	22970
09:15:31.110226	22968
09:15:31.128118	22967
09:15:31.530215	22967
09:15:32.200263	22964
09:15:32.416145	22956
09:15:32.768265	22970
09:15:33.356351	22959
09:15:33.423385	22960
09:15:33.742317	22954
09:15:34.533320	22954
09:15:34.829320	22955

I wanna return it into like this in period of one min.

I don't know how to write the expression in order ti generate this chart.

Re: How to create a candlestick chart???

Mon, 18 Aug 2014 15:07:48 GMT

Hi, There is a sample Application DataVisualization.qvw provided by Qlikview. It should be available in C:\Program Files\QlikView\Examples\Documents.

Anyways I have attached a datavisualization.qvd. It is in the Trends sheet

Re: How to create a candlestick chart???

Mon, 18 Aug 2014 15:19:34 GMT

But how to set to view it as 1 min, 5 min, so on when press a button, THZ

Re: How to create a candlestick chart???

Mon, 18 Aug 2014 15:28:18 GMT

You have to create Timeperiod as the Dimension and then create multiple expressions working for different timeperiods

For clicking the button. You can have various buttons(behind the scene with variables). for the selected click of the button, you can conditionally enable/disable the expression in the chart.

- Ram

Re: How to create a candlestick chart???

ThornOfCrowns — Mon, 18 Aug 2014 15:32:00 GMT

Does the example provided by ramko496 meet your original requirement of showing the data in 1 minute chunks?

You're now asking a different question - i.e. how do I add a button to change the Time Period?

You've also given a very vague requirement for this "1 min, 5 min and so on" - where does the "and so on" stop - 10 minutes, 15? 30? 60?

Re: How to create a candlestick chart???

aveeeeeee7en — Mon, 18 Aug 2014 15:34:38 GMT

This can be achieved by using Box-Plot Chart.

See this thread for Answer:

Daily Trend Graph

The Answer is provided by Avinash R

Also, see the Attachment.

Regards

Aviral Nag

Re: How to create a candlestick chart???

Wed, 20 Aug 2014 02:27:00 GMT

Thz a lot, I will try the examples that guys provided to me.

But I want to know more about the code how to write because I am not familiar to write program

Re: How to create a candlestick chart???

Siva_Sankar — Wed, 20 Aug 2014 02:39:54 GMT

Hi Terrrance,

For the code and how it work, refer the blog http://community.qlik.com/blogs/qlikviewdesignblog/2014/08/18/recipe-for-a-box-plot by hic. For similar example, refer the thread here Box plot graph

-siva

Re: Re: How to create a candlestick chart???

Wed, 20 Aug 2014 02:56:09 GMT

Do I need to change excel or MS Access format??

shankarece wrote:

Hi Terrrance,

For the code and how it work, refer the blog http://community.qlik.com/blogs/qlikviewdesignblog/2014/08/18/recipe-for-a-box-plot by Henric Cronström. For similar example, refer the thread here Box plot graph

-siva

Re: Re: How to create a candlestick chart???

Siva_Sankar — Wed, 20 Aug 2014 03:02:34 GMT

Any source files are fine.

Re: How to create a candlestick chart???

Wed, 20 Aug 2014 14:25:05 GMT

When I press 1min button, it shows every period of one min, then I press 5min button, it shows other graph with period of 5min. I wanna to know how to write,,,thz a lots

Re: How to create a candlestick chart???

Wed, 20 Aug 2014 14:56:37 GMT

Maybe I presented not very clear I know how to create a candlestick chart with excel provided with stock high, stock low, stock middle, Box Plot Upper Whisker and Box Plot lower Whisker.

But I get the excel in this format below, plz help me to solve, better to provide how to write the expression, such as if(Avg ([Adj. Close]) > Avg ([Adj. Open]), Avg ([Adj. Close]), Avg ([Adj. Open])) (sth like this)

TIME	LAST
09:14:29.528551	22951
09:15:29.614293	22961
09:15:29.639119	22968
09:15:29.641101	22951
09:15:29.878346	22975
09:15:29.882078	22978
09:15:29.884098	22979
09:15:30.007220	22965
09:15:30.024216	22980
09:15:30.026978	22979
09:15:30.105209	22980
09:15:30.236066	22967
09:15:30.322187	22978
09:15:30.372227	22980
09:15:30.512260	22983
09:15:30.742066	22970
09:15:30.744072	22963
09:15:30.959001	22970
09:15:31.110226	22968
09:15:31.128118	22967
09:15:31.530215	22967
09:15:32.200263	22964
09:15:32.416145	22956
09:15:32.768265	22970
09:15:33.356351	22959
09:15:33.423385	22960
09:15:33.742317	22954
09:15:34.533320	22954
09:15:34.829320	22955

e.g.

1.start from 09:15:29.614293(2nd data) to 09:15:34.829320(last one)

2. group those date to make one candlestick of the chart, present in 1 min

3. compare the LAST of those data in order to make stock high, stock low, stock middle, Box Plot Upper Whisker and Box Plot lower Whisker

Re: Re: How to create a candlestick chart???

Siva_Sankar — Wed, 20 Aug 2014 15:28:24 GMT

I have attached the steps with screenshot to implement box plot. Try referring.

Re: How to create a candlestick chart???

Thu, 21 Aug 2014 06:01:00 GMT

But how to make 09:15:29.614293 into 09:15 for comparison. How to type the expression??THZ

Re: How to create a candlestick chart???

senarath — Thu, 21 Aug 2014 06:07:09 GMT

hi,

You can use formatting as below.Time is your time field name.

=Time((Timestamp#(Time,'MM/DD/YYYY hh:mm:ss')),'hh:mm')

Thanx

Re: How to create a candlestick chart???

Thu, 21 Aug 2014 07:26:50 GMT

How to decrease interval of Y-axis in order to look better

Re: How to create a candlestick chart???

Siva_Sankar — Thu, 21 Aug 2014 07:35:34 GMT

can you upload your qlikview file?

Re: Re: How to create a candlestick chart???

Mon, 25 Aug 2014 02:49:37 GMT

Sorry for late reply

Re: Re: Re: How to create a candlestick chart???

jonathandienst — Mon, 25 Aug 2014 05:26:26 GMT

Just replace your fixed Min and Max with calculated values:

Min(LAST), and Max(LAST)

See attached.

HTH

Jonathan

Re: How to create a candlestick chart???

Mon, 25 Aug 2014 09:43:02 GMT

How can I colour the candlestick, when last-value of LAST greater than open-value of LAST in Green and last-value of LAST smaller than open-value of LAST in Red.