topic Re: Using FirstSortedValue dimension field and min date problem in QlikView

Using FirstSortedValue dimension field and min date problem

paolojolly — Thu, 06 Nov 2014 08:03:31 GMT

Hi All,

I have a problem with the following document:

In a table for each item i have price and date of document fields

I need to have in a a pivot chart for each item the last price in the filtered period and the first and last document date of this price

I tried using FirstSortedValue in dimension but i have a problem with the calculation of the first date.

For example for item 005 last price is 0.94, last doc date for this price is 04/11/2014 and fist date for this price should be 15/10/2013.

Using MIN(DATA_RIF_DOC) for calculation the first date I get 07/01/2009 but it is wrong

I attach a sample document

Can someone help me?

Many thanks

Re: Using FirstSortedValue dimension field and min date problem

jagan — Thu, 06 Nov 2014 09:22:38 GMT

Hi,

Try like this

Temp:

LOAD ITEM,

W_PRZ_LIS,

DATA_RIF_DOC,

DATA_INI_RIGALIS

FROM

(biff, embedded labels, table is Sheet1$);

Data:

LOAD

If(Peek(ITEM) <> ITEM, 1, 0) AS Flag

RESIDENT Temp

ORDER BY ITEM, DATA_RIF_DOC DESC;

DROP TABLE Temp;

Chart : Straight Table

Dimension: ITEM, W_PRZ_LIS

Expression:

Min:

MIN({<Flag={1}>}DATA_RIF_DOC)

Max:

MAX({<Flag={1}>}DATA_RIF_DOC)

Hope this helps you.

Regards,

Jagan.

Re: Using FirstSortedValue dimension field and min date problem

paolojolly — Thu, 06 Nov 2014 09:47:51 GMT

Thank you, But I need to calculate the information directly on pivot chart without using the script. do you think is possible?

Re: Using FirstSortedValue dimension field and min date problem

jagan — Thu, 06 Nov 2014 14:44:38 GMT

HI,

There will lot of issues if you use FirstSortedValue(), if for the same data and item if you same value twice then you will get null value, also using Aggr() has lot of performance issues.

It is possible but bit difficult.

Regards,

Jagan.