Using FirstSortedValue dimension field and min date problem

[paolojolly]

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

[jagan]

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]

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

[jagan]

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.