Booking date and shipment date formula

Sathish_Kanna

Hello I am trying to arrive at a beast mode calculation where the booking in current quarter if shipped in the current quarter then give me the shipment dollars. It has to be dynamic so that I don't have to change the formula every quarter.

MarkSnodgrass

I would use the QUARTER() function and the YEAR() function to ensure that you get the expected result. It would look something like this:

CASE WHEN QUARTER(dt) = QUARTER(CURRENT_DATE()) AND YEAR(dt) = YEAR(CURRENT_DATE()) THEN 'Same'

ELSE 'Different'

END

nmizzell

Do you want the formula to return a date or the shipment dollars? What are the columns in your dataset?

GrantSmith

To get the dollar amounts you can use Mark's logic to return the amount for the quarter:

CASE WHEN QUARTER(`dt`) = QUARTER(CURRENT_DATE()) AND YEAR(`dt`) = YEAR(CURRENT_DATE()) THEN
  `Shipment Dollars`
END

Sathish_Kanna

I have two columns. One is the Booking date and the second is the shipment date. IF the Booking date and the shipment date are within the same quarter then give me the Shipment dollar for the Order number.

GrantSmith

CASE WHEN QUARTER(`booking_date`) = QUARTER(`shipment_date`) AND YEAR(`booking_date`) = YEAR(`shipment_date`) THEN
  `Shipment Dollars`
END

You'd want something like this then

MarkSnodgrass

If you only want to show the orders that meet your criteria, you can create this beast mode and then drag into your filters and filter to include.

CASE WHEN QUARTER(booking_date) = QUARTER(shipment_date) AND YEAR(booking_date) = YEAR(shipment_date) THEN

'Include'

ELSE 'Exclude'

END

Sathish_Kanna

In this case lets say the booking date is 1 October and the shipment date is 15th November then will the output be the Shipment dollars? Should I load a date mapping file to get the output?

MarkSnodgrass

If your dataset already has the booking date, shipment date, shipping dollars and order number, you shouldn't need any additional fields. Have you tried using any of the above suggestions in Analyzer? Start with a table card and see if it returns the results you expect.