Variable based on If condition

[guillaume_gorli]

Hi everyone,

I am trying to build an app regarding customer satisfaction for which i will need some help.

The idea is to determine wether a customer is satisfied because delivered on time. The trick is that i have on my database, key part numbers that have to be delivered very quickly and others that have to be delivered the normal way.

I have then a test database with basic informations :

• Part number
• Codification that determined wether part number has to be delivered urgently or not
• Order number
• Order creation date
• Customer requested date
• Invoicing date

First issue is that i have to determine a leadtime based on the codification :

If part number is linked to codification A, then the customer leadtime is invoicing date versus order creation date

If part number is linked to codification B, then the customer leadtime is invoicing date versus customer requested date

Basically the output of such criteria should be :

Second step is is to determine directly from the app wether the customer is satisfied or not. The rule to apply is that the customer is satisfied  if the leadtime is less than or equal to zero.

Basically the output of such criteria should be :

My guess is that i have to set up variables based on a condition like IF formula in excel but i am stuck

Attached is test database as well as .qvw app for those who want to help.

Thanks in advance

[sunny_talwar · Accepted solution]

Try this script:

LOAD *,

  If([Customer leadtime] <= 0, 'Yes', 'No') as [Customer Satisfied];

LOAD [Part number],

    Codification,

    Order,

    [Order creation date],

    [Customer requested date],

    [Invoicing date],

    If(Codification = 'A', [Invoicing date] - [Order creation date], [Invoicing date] - [Customer requested date]) as [Customer leadtime]

FROM

[Customer satisfaction TEST .xlsx]

(ooxml, embedded labels, table is Feuil1);

Also attaching the qvw file (PFA)

Best,

Sunny

[guillaume_gorli]

Thanks a lot Sunny, this is just perfect.

One last thing : how would you do to make the customer satisfaction indicator to be show in a chart as below ?

[sunny_talwar]

Use this as an expression with no dimension for a bar chart:

=Num(Count({<[Customer Satisfied] = {'Yes'}>} [Customer Satisfied])/Count([Customer Satisfied]), '###0,00%')

Also find attached.

Best,

Sunny

[guillaume_gorli]

Sunny,

the chart you produced is showing wrong results : 54,55 % vs 60 %

(in the example, customer is satisfied 6 times as per 10 orders sample which then makes a 60 % ratio)

[sunny_talwar]

There are actually 11 orders in your sample (6 Yes and 5 No).

[guillaume_gorli]

... and to put an end to all my demands , i need to show a chart for customer satisfaction linked to Codification A and a second chart linked to codification B

[guillaume_gorli]

you are absolutely right

[sunny_talwar]

Add Codification as a dimension to the chart I just made. (PFA)

Best,

Sunny

[guillaume_gorli]

I will.

Thanks a lot for your help Sunny. This help me a lot