topic Re: Comparisons in QlikView

Comparisons

Tue, 06 Jan 2015 11:50:03 GMT

Good Morning. I'm new to QLIK and i'm working to a monitoring dashboard of some equitys. This is an example of an equity's load:

Microsoft:

LOAD year(Date) as Year,

month(Date) as Month,

Date,

Open,

High,

Low,

Close,

Volume,

[Adj Close] ,

'MICROSOFT' as Azione

I would like to compare the close of the equity with the previous closes. For example compare the last close with the close of december to see his trend. How can i do?

Re: Comparisons

tresB — Tue, 06 Jan 2015 12:06:28 GMT

Try like:

Straight table:

Dimension: Month

Exp1: FirstSortedValue(Close, -Date) // Monthwise last close

Exp2: Above(Column(1)) // Monthwise previous month last close

Note: To get proper above() result, you have to sort the chart Month wise.

Re: Comparisons

mdmukramali — Tue, 06 Jan 2015 12:08:20 GMT

Dear,

Can you share the sample data file.

so that we can help you in better way.

Thanks,

Mukram.

Re: Comparisons

isorinrusu — Tue, 06 Jan 2015 12:09:43 GMT

Hi Stefano,

A simple Line Chart having Month as the dimension and Close as the expression should do. Or not?

Re: Comparisons

Anonymous — Tue, 06 Jan 2015 12:20:26 GMT

See below discussion may help you

http://community.qlik.com/message/267055#267055

http://community.qlik.com/message/341282#341282

http://community.qlik.com/message/619584#619584

Re: Comparisons

Tue, 06 Jan 2015 16:21:15 GMT

But how can i have a percentage of the change? For example Apple close went down of 2% compared with last close?

Re: Comparisons

richard_chilvers — Tue, 06 Jan 2015 16:29:56 GMT

Hi Stefano

QV often has various ways to achieve what you want.

To start with I should try the idea given by tresesco (above). This should give you the latest value for this month and the closing value for last month. You can then create a 3rd expression calculating the difference as a percentage.

But you might also look at your script, and when you load the data, it may make sense to have one row for each month, by GROUPing the values for that month (eg. MAX(High).

Good luck.

Re: Comparisons

NickHoff — Tue, 06 Jan 2015 16:39:56 GMT

Stefano,

To achieve this comparison your best option will be to use alternative states. Create one state for the current close and one state for the previous close. In your set subtract the alternate state of previous close by current close.

Another option would be to add the close date to your data model and AGGR by the close date. If you go this route you'll have to Peek the close date.

Re: Comparisons

Wed, 07 Jan 2015 09:13:27 GMT

Thanks Richard and Tresesco! I did it!

Re: Comparisons

richard_chilvers — Wed, 07 Jan 2015 09:18:10 GMT

Good news !