topic Re: Simple Inter-record query in QlikView

Simple Inter-record query

Anonymous — Thu, 28 Feb 2013 15:51:37 GMT

I would like to determine the simple % change in price between one record set and another (between one day and the next), and ideally chart this. However I'm struggling to do this. I can do this in the qvd as part of the sql, but prefer to do it in the qvw as part of the expression.

( ClosingPrice[Current] - ClosingPrice[PreviousDay] )/ClosingPrice(PreviousDay)

On the current record, I'm attempting to query the previous price using the following expression. The expression is for a specific instrument, using the MakeDate function, using LadderYear, LadderMonth and LadderDay as the previous recorde date for that instrument. But this returns NULL value.

=avg(
{$<Instrument1={$(='[DOE Annual World Oil Demand]')}, LadderDate={$(=MakeDate($(LadderYear),$(LadderMonth),$(LadderDay))) }>} ClosingPriceUSD1
)

I have also tried the function Last. This also returns NULL value.

=Last( {$<Instrument1={$(='[DOE Annual World Oil Demand]')}>} ClosingPriceUSD1 )

I'm missing something fundamental here, as I would have thought this to be relatively simple to do. Any guidance people can offer, will be greatly appretciated.

Many thanks

Re: Simple Inter-record query

Thu, 28 Feb 2013 16:36:42 GMT

I just had this problem!

If you look at your chart as a straight table, you will see that the days are arranged vertically.

Try closingprice - above(closingprice) / above(closingprice) and it should straighten things out.

Re: Simple Inter-record query

Anonymous — Thu, 28 Feb 2013 16:56:40 GMT

Thank you for the helpful response. I wasn't aware of the "above" function. But similiar to the Last function, it doesn't seem to like the expresion

Above(

{$<Instrument1={$(='[DOE Annual World Oil Demand]'))}> ClosingPriceUSD1

)

Re: Simple Inter-record query

Anonymous — Thu, 28 Feb 2013 17:11:59 GMT

Thank you for the helpful response. I wasn't aware of the "above" function. But similiar to the Last function, it doesn't seem to like the expresion.

Are these functions invalid in expression with set analysis?

Above(

{$<Instrument1={$(='[DOE Annual World Oil Demand]'))}> ClosingPriceUSD1

)

even the following doesn't work

=Above(ClosingPriceUSD1)

returns NULL

Re: Simple Inter-record query

Thu, 28 Feb 2013 17:42:30 GMT

Those functions (last, above, etc.) work fine with set analysis. I am using them with set analysis in several applications...

How are you looking at your data? I recommend looking at it as a straight table while you are figuring out all of your equations, and then charting the results, rather than trying to do it as a bar or line chart directly.

The Chart inter-record functions help info is very useful as well!

Re: Simple Inter-record query

Anonymous — Thu, 28 Feb 2013 17:48:04 GMT

I'm using a simple table with the dimension of LadderYear,LadderMonth,LadderDay, and Instrument.

Expression as ClosingPriceUSD and trying to create an expression for the previous day ClosingPriceUSD using Last, Above, MakeDate....anything that allows me to move across the record set without changing the current record.

Re: Simple Inter-record query

Thu, 28 Feb 2013 17:51:22 GMT

If you want to show me your data, I'll see if I can do anything with it.

Re: Simple Inter-record query

Thu, 28 Feb 2013 18:34:40 GMT

Oh, I know why your chart inter-record functions aren't working!

It's because you have too many dimensions.

If you go LadderYear, LadderMonth, LadderDay, QlikView will look at all of the records in one "Day" as a block, and inter-record functions only function within a block.

For example, I had a bug where I sorted by Year(date) and Month(date) and did month/month analysis.

Using the code sum(policies) / above(sum(policies)),

My table looked like:

2012 2013

Oct Nov Dec Jan Feb

- 97% 98% - 96%

However using MonthName(date) I was able to have January show up as non-blank.

Rather than creating Year, Month, and Day, I recommend just using Date. Above() will then work normally.

Re: Simple Inter-record query

Anonymous — Thu, 28 Feb 2013 18:40:08 GMT

Here's the data - nothing really compilcated at all.

Expression for previous price is =above({$<Instrument1={$('[DOE Annual World Oil Demand]')}>} ClosingPriceUSD1)

Re: Simple Inter-record query

Thu, 28 Feb 2013 18:46:51 GMT

Yeah, like I thought, it's because of your dimensions. If you're looking for year over year comparisons, try re-ordering your dimensions like this:

Dimensions:

Instrument1

Month

Day

Year

and you should be able to use the above() function.

Re: Simple Inter-record query

Anonymous — Thu, 28 Feb 2013 19:05:03 GMT

I really appretciate your help and suggestions. I tried to rearrange the dimensions as suggested and once again it just doesn't seem to like the previous price. I'm either doing something so silly or it's fundementally wrong.

The only data set I have are 31st Dec for each year - there is no pricing during the year for this particular instrument. So rearranging the dimension have no effect. I even replaced the Month,Day, Year with a simple date and of course that had the same effect.

I've done some relatively complex charts with Qlikview and I would have thought a % change in any value over a period is relatively simple to do. Maybe i should rename Discussion as "Not so simple inter-record query".

If setanalysis works with Last, Above, it must be something in my expression that it doesn't like. Does it matter on the order whether I do Above(sum(......)) or Sum(above(.....))?

Re: Simple Inter-record query

Thu, 28 Feb 2013 19:23:34 GMT

Okay, I actually played around with my data to see what was going on.

Basically, it looks like your Instrument1 isn't changing. If you had 2 different instrument1s, you'd have a table like this:

Instrument, date, current price, previous

DOE A, dec31 1970, 46808, -

DOE B, dec31 1970, 46707,46808

Unfortunately, it doesn't look like this is dependent on the order the dimensions are in. In a pivot table, it would be, but this is a straight table.

If you make separate tables for each instrument (which might be easier to read anyways, given how large a period of time your data spans) and just use date as a field rather than month/day/year, I think that'll do it.

It's really too bad Qlikview can't get charts to run off of pivot tables rather than straight tables. It'd be much more intuitive.

Re: Simple Inter-record query

Thu, 28 Feb 2013 19:27:17 GMT

Alternately, this person seemed to create a weird date alias to get around the same problem. It's common, if it makes you feel any better!

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