Customer satisfaction based on IF conditionS

[guillaume_gorli]

Dear All,

I am trying to build an application that will determine wether a customer is satisfied or not.

I have a list of invoices stored in an excel file with different informations :

Invoice, being the invoice reference.

[Part Number], being the part number invoiced.

[Safety Stock], being the safety stock against the part number.

[Invoicing date], being the date the part number has been invoiced.

[Order creation date], being the date the customer order has been created.

[Customer requested date], being the date the customer requested to be invoiced.

Qty being the quantity invoiced to the customer.

To determine wether a customer is satisfied there are two situations.

• If Part number is covered by a safety stock ([Safety Stock] is different than zero) :

in this case a customer is satisfied if the invoicing date is the same as the order creation date (meaning the order is shipped the day the order is created).

• If part number is not covered by a safety stock ( ([Safety Stock] is equal to zero or "empty") :

In this case a customer is satisfied if the invoicing date is at least equal to the customer requested date. The same is the order is invoiced before it was requested by the customer date.

Knowing those two rules, the test table shall return a customer satisfaction of 50 % as 4 part number among 8 parts number have been invoiced on time.

Attached is qvw and excel test database.

Thanks in advance

Guillaume

[sunny_talwar]

May be this

=If([Safety Stock] = 0 or Len(Trim([Safety Stock])) = 0,

  If([Invoicing date] <= [Customer requested date], 'YES', 'NO'),

  If([Invoicing date] = [Order creation date], 'YES', 'NO'))

[guillaume_gorli]

Looks great Sunny.

How would I sum this up in a chart or in a text box in a percentage figure ? (50 % in our example)

Guillaume

[sunny_talwar · Accepted solution]

May be like this

=Num(Sum(If([Safety Stock] = 0 or Len(Trim([Safety Stock])) = 0,

If([Invoicing date] <= [Customer requested date], 1, 0),

If([Invoicing date] = [Order creation date], 1, 0)))/Sum(Qty), '##%')

[guillaume_gorli]

Perfect !

I will have to show this indicator based on different criteria (company, period), so i may have to "play" a bit with your formulas, but i reckon i will be able to do it by myself.

Thanks a lot

[guillaume_gorli]

Sunny,

How would you write below formulas if i would like to show in the table only part number A ?

=If([Safety Stock] = 0 or Len(Trim([Safety Stock])) = 0,

  If([Invoicing date] <= [Customer requested date], 'YES', 'NO'),

  If([Invoicing date] = [Order creation date], 'YES', 'NO'))

I tried :

=If{<[Part Number]= {'A'}([Safety Stock] = 0 or Len(Trim([Safety Stock])) = 0,

  If([Invoicing date]  <= [Customer requested date], 'YES', 'NO'),

  If([Invoicing date] = [Order creation date], 'YES', 'NO'))

but this does not seem to work

[guillaume_gorli]

I would like to get below output for instance :

[sunny_talwar]

May be this

=If(Only({<[Part Number]= {'A'}>} [Safety Stock]) = 0 or Len(Trim(Only({<[Part Number]= {'A'}>} [Safety Stock]))) = 0,

  If(Only({<[Part Number]= {'A'}>} [Invoicing date]) <= Only({<[Part Number]= {'A'}>} [Customer requested date]), 'YES', 'NO'),

  If(Only({<[Part Number]= {'A'}>} [Invoicing date]) = Only({<[Part Number]= {'A'}>} [Order creation date]), 'YES', 'NO'))

[sunny_talwar]

Or this

=Only({<[Part Number]= {'A'}>} If([Safety Stock] = 0 or Len(Trim([Safety Stock])) = 0,

  If([Invoicing date] <= [Customer requested date], 'YES', 'NO'),

  If([Invoicing date] = [Order creation date], 'YES', 'NO')))

[guillaume_gorli]

Thanks a lot Sunny.