Multi Field Formula

Hi All,

I am trying to use a multi-field formula for a case where I have a customer id, basket id and Offer Code as columns. I want to find out the the number of times a booking was made with an offer which is count distinct of basket ids with offers but cannot as currently in the table one basket has Null values which refers to something else. For that I thought one way could be add a column which will assign "offer" to all same basket ids if one of them has an offer and then do a count distinct. (Please suggest an alternative solution in case you think it would be easier ) . I tried using the multi field formula ( "IF [Basket ID ]=[Row-1:Basket ID ] and [Row-1:Price Code]!= 'Null' THEN 'Offer' ELSE 'no offer' ENDIF) but it is not giving desired result.

I have added an excel sheet of my sample data and have added a column D which I would want ideally to finally count distinct the number of basket ids with offers.

Would highly appreciate any help

Thanks

Veekay

MF Formual Test.xlsx

Common Use Cases

Accepted answers

Kenda

@veekay I believe I was able to accomplish what you are wanting with the provided sample data. See if the attached 11.0 workflow helps you solve your problem. There is only one row for which we got different answers, so it may have been mis-coded previously. Does this help?

pricecode veekay.yxmd

Kenda

Hey @veekay!

I would recommend a Cross Tab in this situation. Group by Reservation ID then make the New Column Headers the Hotel Code and the Values for New Columns the Total Revenue. Use Sum as the Method for Aggregating Values. Then, add a Formula and create your new column with the following expression:

if [PACKAGE]=0 then "Free Package" elseif isnull([PACKAGE]) then "No Package" else "Paid Package" endif

I then used a Find and Replace tool to add this column back to the original set of data, setting Reservation ID as both the Find Within Field and the Find Value then choosing Append Field(s) to Record and selecting Package Detail. Note the Reservation ID must be string in order to do this. See the attached v11.0 workflow, and let me know if this solves your issue!

veekay packages.yxmd

All comments

Kenda

Hey @veekay! Would it work to just filter out the null values before counting distinct?

veekay

Hi @BarnesK,

I do not want to filter out "null' values as they refer to other additional items in the basket . So just for the sake of measuring the number of bookings with offers I thought would work better. Filtering out "null" also would not work because I would need to compare bookings (countd basket ids) with offers and bookings without offers and 'null' values would be counted as bookings without offers. I can do a CNTD for all basket ids and subtract CNTD for ones with Price code (offers) but I want to use this additional column in my Tableau visualisation

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703