Change the timeline for one type of status

Question & Answer
, ,
1

Hello everyone.
Have some issues with vertically stacked bar charts and timelines.

A parcel has 3 types of statuses: “sent”, “delivered” and “returned”.
Status “sent” and “delivered” parcels were received in one day (all time) and the status “returned” is optional and has a delay from 1 to 7 days compared to the date of delivery.
I need to create a new timeline for parcels in order to timeline “sent” and “returned” displayed in one day.
This is to be able to show all statuses of a day in one bar of a chart.

Sorry, can’t show it exact data, only “dumb” on the screenshot.

image
image784×160 5.71 KB

2

Hello @alexmiko , welcome to the QS community!

My understanding is that you want to show a particular value in the new_timeline field that in this case would be a calculated field that shows a date that is calculated based on the data&time following this logic:

  • For sent or delivered there is no variation as it happens within the same day
  • For returned parcels you need to add more days to the new_timeline field

If thats the case you can use the addDateTime function to add X days to the data&time value. Code would loook like this:

addDateTime(7, DD , {data&time})

Hope this address your question, otherwise please share with us a concept of the visualization you are trying to achieve to understand better the issue.

Happy dashboarding!

1 Like
3

Hello @EnriqueS

Unfortunately, the delay isn’t consistent as I say. It is sometimes 1 day, sometimes 3 days, sometimes 7 days(ets.).
If I will use addDateTime(7, “DD” , {data&time}) then I have another time shift.

I need some formula that can calculate the difference in time between delivered and returned rows. After that be able to locate this formula to your proposed formula on the “7” place.

4

Hi,

so you need the date for the returned status to be the same as the sent status?

If we assume, that the sent date is always the earliest, then a minOver should work.

minOver({data&time},[order_id,user],PRE_AGG)

You have to create this caluclated field in the dataset to really use it in timelines.

2 Likes
5

Hello Alex, thanks for the clarification.

In that case the table calculation functions would be helpful here.

I can think on the lag function as the ideal candidate here, as you will need to get the previous date&time value for a given parcel (this is what you will use to partition) and then do the math with the aforementioned addDateTime function.

Something like this:

lag
(
     max({data&time}), 
     [{data&time} ASC], 
     1, 
     [order_id]
)

EDIT: I see @thomask also replied while I was writing, a minOver could help here as well, its another calculation function. Lag allows you to have control to get the specific Nth previous value in a series, which as you mention that delivered would be always the previous state for a returned package I thought it would be appropriate here, but both options should work

Hope it helps!

2 Likes
6

@thomask @EnriqueS Big thanks for the bits of advice!
The solution with “minOver” works well.

2 Likes