topic Re: Help in expression in QlikView

Help in expression

ali_hijazi — Mon, 02 Mar 2015 20:16:01 GMT

Hello got a pivot table with 2 dimensions (Month-Year) and Category

I got an expression very simple sum(Amount)

now what I want is demonstrated in the following image:

so for each month-year (I need to display the value of the last year's month-year

Please advise

Re: Help in expression

terezagr — Mon, 02 Mar 2015 20:52:07 GMT

Have you tried to have a look at Peek() function?

Peek function allows you to 'peek' to number of records you specify.

in your load script you can add this and you will 'peek' 1 record in your read table:

e.g. Peek(Dates,-1) as Peek_dates

Re: Help in expression

ali_hijazi — Mon, 02 Mar 2015 20:53:24 GMT

I don't know how many combinations the end user may select

so it is not feasible to make it in the script as the selections are endless

Re: Help in expression

stabben23 — Mon, 02 Mar 2015 20:53:53 GMT

Hi, try this:

above(Total above( Total sum( volume)))

Re: Help in expression

terezagr — Mon, 02 Mar 2015 20:59:30 GMT

The whole script:

Table:

LOAD Dates,

Categorie_Flag,

Volume

FROM

load.txt

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LOAD

Dates,

Categorie_Flag,

Volume,

Peek(Volume,-2) as Peek_Table

Resident Table

Order by Dates;

And I got this:

Re: Help in expression

terezagr — Mon, 02 Mar 2015 21:03:49 GMT

Staffan solution is cool, but as soon as you select e.g. 2013 year, it will not show you anything for volume. Despite the fact that there were volume data for 2012. See picture below:

Re: Help in expression

stabben23 — Mon, 02 Mar 2015 21:09:52 GMT

And much cooler:

above( total above( Total sum({1} volume)))

Re: Help in expression

terezagr — Mon, 02 Mar 2015 21:15:43 GMT

And might affect performance of the application more than the Peek ()

Re: Help in expression

terezagr — Mon, 02 Mar 2015 21:30:55 GMT

With the {1} you will always evaluate the full record set of all the data in the document - ignoring all your selections:

With peek() function:

Re: Help in expression

terezagr — Thu, 05 Mar 2015 15:31:27 GMT

That's why you should use the Peek() as I suggested

Table:

LOAD Dates,

Categorie_Flag,

Volume

FROM

load.txt

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LOAD

Dates,

Categorie_Flag,

Volume,

Peek(Volume,-2) as Peek_Table

Resident Table

Order by Dates;