show last known value until new vaule is available, chart expression, date as X-axis

[NZ_QV_Guy]

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)

 

[PradeepReddy]

Create a master calendar.
Create a bar chart with,
Dimension: Date field from Master Calendar.
Expression: label the expression as 'Price_expr_label'
If(isnull(PRICE),above(rangesum(Price_expr_label),1),sum(PRICE))

Note: this process will work if you are showing the full results. If you selects a month, then the chart shows correctly from the first sales date, prior to that it shows zero.

[Anil_Babu_Samineni]

Do you have full data? Or you want to generate Calendar?

[vikramhssa]

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))

[jonathandienst]

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.