Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have 2 columns
1 - releasedate(without time stamp)
2- Shiptime ( date with time stamp)
I have to have a condition in my data such that ...i need to count the id's based on shiptime and release date
If the release is on say Aug 30 , and ship time is one day before aug 30, that is aug 29th and after 1:00pm , then put it in 'with in 1 day',if ship time is one or more days before 1 pm , then put it in '1 day or more'
How do i write this condition for a pie chart ?
I am bit confused ..
If I understood your problem correctly, I would use something like this as flag in the script or as calculated dimension:
if( (ReleaseDate - ShipTimestamp) <= 11/24, 'within 1 day ','1 day or more ')
Dates and Timestamps are numericals in principal, so you can just subtract two dates / timestamps (a timestamp is a date with decimal places). 11/24 is indicating 11 hours, which is the difference between midnight and 1:00 pm the day before, right?
(I noticed though that a Shiptimestamp of exactely 1 pm on Aug 29th and a release date of 30 Aug lead to 1 day or more, one second later and it fell within 1 day).
If I understood your problem correctly, I would use something like this as flag in the script or as calculated dimension:
if( (ReleaseDate - ShipTimestamp) <= 11/24, 'within 1 day ','1 day or more ')
Dates and Timestamps are numericals in principal, so you can just subtract two dates / timestamps (a timestamp is a date with decimal places). 11/24 is indicating 11 hours, which is the difference between midnight and 1:00 pm the day before, right?
(I noticed though that a Shiptimestamp of exactely 1 pm on Aug 29th and a release date of 30 Aug lead to 1 day or more, one second later and it fell within 1 day).
This gives me correct answers , thank you very very much , you made my expression so simple , i have been trying multiple ways with if statements in the scripts.
Could you please explain what is 24 doing in the expression? i understood 11 is the difference between midnight and 1:00pm , and how are we getting correct values by dividing 24 ?
Thanks a ton.
Hours are represented as decimal places / fractional part of a timestamp value, where the whole integer number represents days (1 per day). If a difference of 1 represents one day, 1/24 represents one hour (24 hours a day).
I noticed also that if your shipment is later than release date, it will still count to within 1 day, you could catch that if you want with an additional if.
Stefan