Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Comparisons

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?

1 Solution

Accepted Solutions
MVP
MVP

Re: Comparisons

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.

9 Replies
MVP
MVP

Re: Comparisons

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.

mdmukramali
Valued Contributor II

Re: Comparisons

Dear,

Can you share the sample data file.

so that we can help you in better way.

Thanks,

Mukram.

isorinrusu
Contributor III

Re: Comparisons

Hi Stefano,

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

balkumarchandel
Valued Contributor II

Re: Comparisons

Not applicable

Re: Comparisons

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

richard_chilvers
Contributor III

Re: Comparisons

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.

qvelhoff
Valued Contributor

Re: Comparisons

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.

Not applicable

Re: Comparisons

Thanks Richard and Tresesco! I did it!

richard_chilvers
Contributor III

Re: Comparisons

Good news !

Community Browser