MTD vs LMTD

Q&A
, ,
1

Hi,

Goal: Create a table that creates comparison of values between latest booking date (MTD) vs same period last month (LMTD). One example is cum conversion rate. If it happens that LMTD is not existing for this month, e.g March 30 vs Feb 30, it will just get the max booking date of LMTD which is Feb 28. Another scenario is if MTD is March 15 but there is no booking date on Feb 15, it just get Feb 14. Then, I want to compute % of increase/decrease from last month

Illustration:

image
image1332×420 36.7 KB

Variables:

  • Booking Date
  • Conv Rate = {BOOKING_TAG}/{total_leads_gen_count}
  • Cumulative Conversion Rate = runningSum
  • (sum({BOOKING_TAG}), [BOOKINGDATE ASC])/(max({EARLY_LEADS_GEN_COUNT_CLEAN})+max({MID_LEADS_GEN_COUNT_CLEAN}))
2

Hi @johnjasonalfonso ,

Thank you for outlining the context of your situation. May you elaborate a bit more on what your question is or what specific part you are running into some difficulties with?

3

Hi @JacobR , thanks for replying.

My goal is to be able to create that table below New Table Comparison which comprise of the following

  • Get the latest Cumulative Conversion Rate for March possible (March 3) since today is March
  • Get the Cumulative Conversion Rate from last month which is Feb, that is, same period last month. However if it sees March 30 but there is no Feb 30, then it will get Feb 28. Another example is March 18 MTD but there is no Feb 18, then it will get Feb 17 (latest LMTD<=MTD).
  • Then compare these two values and get % difference

I hope this clears this up

4

Also, I want to get booking # combined with cumulative conversion rate in the new table comparison. So this new table comparison is basically an executive table that summarizes all possible MTD vs LMTD values. Again, if the current day of the month does not exist in the previous month (e.g., March 30 vs. February), or if data for that specific day is missing, the system should default to the latest available date in the previous month that is less than or equal to the current day.

image
image714×412 18.1 KB

5

Hi @johnjasonalfonso,

In terms of ensuring that you get the right dates for MTD and LTMD, I would create a parameter based on your date field and set up a relative date (as shown below):

image
image593×678 27.6 KB

I am not sure if by doing so if it would be able to bring up the latest available date if it is not available (or if it will simply show up no data), but I would definitely recommend trying that out and see how far along that gets you with migrating that goal to Quick. If you have any further questions regarding this process and/or making calculated fields for those cumulative calculations later on, please feel free to send another reply.

Thank you!

6

Hi, @johnjasonalfonso ,

I wanted to touch base since it has been a while since we last had an update on this thread. Were the resources mentioned above able to help with your situation?

Please feel free to reply if you have any outstanding questions or need anything clarified further. If we don’t hear back within the next 3 business days, I’ll go ahead and mark this thread as solved.

Thank you.

7

it didnt help, can you point me to alternative?

8

Hi @johnjasonalfonso,

Hope everything is well! Just so I can understand your current process a little better, when trying the workaround I provided earlier, did it just show a message like “No data,” or did you simply find that this workaround does not apply well for your use case in general? Any feedback/specifics will be greatly appreciated, as that could help me potentially find another workaround for your goal.

Thank you!