Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
=Max({<Item={$(=Only(Item)),Date=}>} Price)
Regards!
here you are ignoring date so the same value will be on all months
then I want to display for March, and April the value $15.69
yes but on may I don't want to have 15.69
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...
This may help:
Creating a Date Interval from a Single Date
oh yeah I just forgot about Interval match
thank you
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!!!