Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a requirement to show a price over time (continuous timeline) where data points only exist when the price changes.
Example Data:
COMPONENT | DATE | PRICE |
A | 1-Jan-18 | $1.00 |
A | 10-Jan-18 | $1.10 |
A | 20-Jan-18 | $1.05 |
Requirement is for a chart (bar, line or table) to use Date as x-axis dimension, which should be continuous (i.e. 31 points for January 2018) and show the last known value until a next value is available. That is show $1.00 from 1-Jan-18 through 9-Jan-18, then $1.10 from 10-Jan-18 through 19-Jan-18, then $1.05 from 20-Jan-18 onwards.
I'm using very basic data here for the purpose of demonstrating. Obviously I could create distinct values through my load script, however in reality the number of COMPONENTS is significantly more, and over a number of years this would result with hundreds of millions of records.
Does anyone know of an "Peek" like function for a chart expression. Above/Before do not work as the no. of dates between the last data point will vary. FirstSortedValue where Date < Date but when Date is the chart expression it obviously wont find data outside that date.
End result, something like below (in it's simplest form)
Pradeep's solution is perfect , adding the component in set analysis will help you select component as well.
If(isnull(Price),above(rangesum(Price_expr_label),1),sum({<Component
=>}Price))
Above() will not always work when users make selections. Better to fill in the data in the load script. Assuming your data is in a fact table called "Fact", then this should work:
T_Pricing: LOAD Distinct DATE Resident Fact; Concatenate(T_Pricing) LOAD Distinct COMPONENT Resident Fact; Left Join (T_Pricing) LOAD DATE, COMPONENT From T_PricingSource; Pricing: LOAD DATE, COMPONENT, If(COMPONENT = Previous(COMPONENT), If(IsNull(PRICE), Peek(PRICE), PRICE)) as PRICE Resident T_Pricing ORDER BY COMPONENT, DATE; DROP TABLE T_Pricing;
Now you will have prices for all dates in the Fact table.