topic Re: Variable based on If condition in QlikView

Variable based on If condition

guillaume_gorli — Tue, 28 Apr 2015 16:04:24 GMT

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

Re: Variable based on If condition

sunny_talwar — Tue, 28 Apr 2015 16:28:32 GMT

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

Re: Variable based on If condition

guillaume_gorli — Tue, 28 Apr 2015 17:14:23 GMT

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 ?

Re: Variable based on If condition

sunny_talwar — Tue, 28 Apr 2015 17:20:00 GMT

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

Re: Variable based on If condition

guillaume_gorli — Tue, 28 Apr 2015 17:33:16 GMT

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)

Re: Variable based on If condition

sunny_talwar — Tue, 28 Apr 2015 17:35:00 GMT

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

Re: Variable based on If condition

guillaume_gorli — Tue, 28 Apr 2015 17:36:52 GMT

... 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

Re: Variable based on If condition

guillaume_gorli — Tue, 28 Apr 2015 17:40:03 GMT

you are absolutely right

Re: Variable based on If condition

sunny_talwar — Tue, 28 Apr 2015 17:41:09 GMT

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

Best,

Sunny

Re: Variable based on If condition

guillaume_gorli — Tue, 28 Apr 2015 17:45:08 GMT

I will.

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

Re: Variable based on If condition

sunny_talwar — Tue, 28 Apr 2015 18:19:19 GMT

No problem. Pleasure is all mine

Best,

Sunny