Hi Yan Wu,
If I understand correctly, you want a point value of 1 before your first point in the dataset, and then for your first point in the dataset, you want 1+change, and then previous+change, and so forth. To do this, you would have to pad your dataset with another point (probably the day before your first point in the dataset) with a value of 1.
Otherwise, you could not display the 1.0 origin point, and start with the first deviation from 1.0, using:
if index()=1 then 1+SUM([Pct chg])
else (1+sum([Pct chg]))*PREVIOUS_VALUE(1)
If my reply doesn't seem to fit, I may need more information to understand your question fully.
Yes you understand it fully,t hank you.
Pad my data with another day - can I do this within Tableau?
It would be bad practice for me to try to alter my dataset natively wouldn't it?
You can pad your data with another day within Tableau by changing the first (or last) day of your data to the day before. Now with a combination of "Showing Missing Values" and a table calculation, you will be able to achieve what you want.
Here are the steps:
- Create a calculated field changing your first day to a day before. Your calculation can be like this one:
- Rebuild the chart using this new Date (Ajusted) field as discrete dates.
- Right click on the date axis and select Show Missing Values.
- Now adjust your formula and make it like this:
I also attached the workbook, so you can have a better understanding.
Nice Solution, Hil.
Yes, Yan Wu, I would typically say padding the data at the source wouldn't be a best practice, so Hil's solution may be the right way to go.