Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paolojolly
Creator
Creator

Using FirstSortedValue dimension field and min date problem

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

3 Replies
jagan
Luminary Alumni
Luminary Alumni

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.

paolojolly
Creator
Creator
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.