Multi Field Formula

[veekay]

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

[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 

[Kenda · Accepted solution]

@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?

[veekay]

Thank you @BarnesK.

Exactly what I looking for.

Regards

[veekay]

Hi @Barnesk,

Can you please help me with another similar request? I am trying to add a column in which value of the rows would depend on one of the variables . I have attached a sheet wherein I want the final result as column H.  I have managed to get column G using a multi field formula but am unable to get Column H. The value of column H depend on the following :

- the reservation id is the same and total revenue of Hotel Code 'Package' >0 then it is a "Paid Package" 

- the reservation id is the same and total revenue of Hotel Code 'Package' =0 then it is a "Free Package" 

- There is no Hotel Code 'Package' then it is 'No Package'

I tried to use the multiplied formula with logic grouped by Reservation id, as below but it seems to change only the row+1 to Paid package, for eg. and the rows further remain with the same value.

 IF [Reservation ID] = [Row-1:Reservation ID] AND [Row-1:Package Detail]="Paid Package" THEN "Paid Package" ELSE [Package Detail] ENDIF

Please guide if there is a better way of doing it

Thanks

[Kenda · Accepted solution]

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]

Hi @Kenda

This is awesome , I never tried the find and replace tool but now I understand it can be of great value .

Thank you

Regards

Varun