topic Re: Customer satisfaction based on IF conditionS in QlikView

Customer satisfaction based on IF conditionS

guillaume_gorli — Mon, 24 Apr 2017 13:57:52 GMT

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

Re: Customer satisfaction based on IF conditionS

sunny_talwar — Mon, 24 Apr 2017 14:13:39 GMT

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'))

Re: Customer satisfaction based on IF conditionS

guillaume_gorli — Mon, 24 Apr 2017 14:19:53 GMT

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

Re: Customer satisfaction based on IF conditionS

sunny_talwar — Mon, 24 Apr 2017 14:36:13 GMT

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), '##%')

Re: Customer satisfaction based on IF conditionS

guillaume_gorli — Mon, 24 Apr 2017 14:43:59 GMT

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

Re: Customer satisfaction based on IF conditionS

guillaume_gorli — Tue, 25 Apr 2017 09:38:16 GMT

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

Re: Customer satisfaction based on IF conditionS

guillaume_gorli — Tue, 25 Apr 2017 09:40:52 GMT

I would like to get below output for instance :

Re: Customer satisfaction based on IF conditionS

sunny_talwar — Tue, 25 Apr 2017 09:48:35 GMT

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'))

Re: Customer satisfaction based on IF conditionS

sunny_talwar — Tue, 25 Apr 2017 09:49:10 GMT

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')))

Re: Customer satisfaction based on IF conditionS

guillaume_gorli — Tue, 25 Apr 2017 09:57:56 GMT

Thanks a lot Sunny.