help in expression

[ali_hijazi]

Hello

I have a pivot table with Product, and Month-Year as dimensions

I'm using the following expression to show the latest price (unit price) of the product per month:

FirstSortedValue(Price,-Date)

Now suppose an Item was bought in Feb 2016 for $15.69 and then in May for $14 let's say, then I want to display for March, and April the value $15.69

Please advise

[maksim_senin]

Hi Ali,

Don't you have a sample? It's hard to help without seeing of data and structure since there are a lot of variants which are applicable under different circumstances.

Best regards,

Maxim

[Anonymous]

=Max({<Item={$(=Only(Item)),Date=}>} Price)

Regards!

[ali_hijazi]

here you are ignoring date so the same value will be on all months

[Anonymous]

then I want to display for March, and April the value $15.69

[ali_hijazi]

yes but on may I don't want to have 15.69

[ali_hijazi]

the data looks something like this for one product:

The data is something as follows:

Date       Product        Value

15/2/2016  P1              15.69

25/2/2016  P1              16.5

16/3/2016  P1               17.23

5/7/2016    P1               16.75

....

for the above product I want to display the following:

for Feb   16.5

for Mar    17.23

for Apr,May, June 17.23

and Jul 16.75 and so on...

[jonathandienst]

This may help:

Creating a Date Interval from a Single Date

[ali_hijazi]

oh yeah I just forgot about Interval match

thank you

[Anonymous]

Then,

If you don't want to modify your script, you can use Above expression.

=If(Count(DISTINCT Value)=0,Above(Sum(Value),1),Sum(Value))

Else, you can do it with Intervalmatch or calendarice your values:

Data:

LOAD *,

    Date(DateInc)    as Date;

LOAD * INLINE [

    DateInc, Value

    01/05/2016, 10

    01/06/2016, 20

    01/08/2016, 50

];

LEFT JOIN

LOAD

    Date,

    If(IsNull(Previous(Date)),'31/12/2016',

        Previous(Date)-1) as PrevDate

RESIDENT Data

ORDER BY Date DESC;

Calendariced:

Load *,

    Month(Date)        as Month,

    Year(Date)        as Year,

    MOnthName(Date) as MonthYear;   

LOAD 

    Value,

    Date + Iterno()-1 As Num,

    Date(Date + IterNo() - 1) as Date 

RESIDENT Data

While Date + IterNo() -1 <= PrevDate;       

DROP TABLE Data;

EXIT Script;

And your expression:

=FirstSortedValue(Value,-Date)

Regards!!!