Compare 2 fields in set analysis

[#1]

Hello,

I am using qlikview 11.

I have 2 fields RequiredDate and DeliveryDate.

I have created a pivot table, showing the data of orders in a month.

I have  to show Order Date, Required Date, Delivery Date and Order Qty and Delivered Qty.

All columns except Delivered Qty are working fine.

For Delivered Qty, I have to show how much qty is delivered on or before RequiredDate.

I have written following expression for it:

sum({< DeliveryDate = {'<=$(RequiredDate)'} >} DeliveredQty)

But this is not working.

For Eg: dates in dd/mm/yyyy format

OrderDate: 5/6/2012 order qty: 144

Required Date: 7/6/2012

Delivered Date: 11/6/2012: Delivered Qty: 144

So my deliveredQty should show 0, because as on 7/6/2012 nothing was delivered.

Please can anyone, correct my expression.

[tresesco]

Pankaj Khairnar wrote:

how much qty is delivered on or before RequiredDate.

.,.....

So my deliveredQty should show 0, because as on 7/6/2012 nothing was delivered.

On or before/ ON ? Are you selecting the dates and trying to see the results? Can you post a sample?

[#3]

no I am not selecting the date.

Required date and Delivery date are my fields against each order no.

Required date and deliverydate i have added as columns.

so against each row, i have order no, required date and delivery date.

so each row may have different required date and delivery date.

[tresesco · Accepted solution]

In that case, I guess you won't be able to achieve the same using set analysis, because set analysis works outside the dimension and once for every object, so that would not compare the RequiredDate row-wise. You can try with IF. If you face challenge there too, please consider to share the application with scrambled data.

[CELAMBARASAN]

Hi,

Try with this

Sum({< DeliveryDate = {"=DeliveryDate<=RequiredDate"} >} DeliveredQty)

Hope that helps

Celambarasan

[#6]

Hi Celambarasan,

I tried the expression given by you, but it works only if customer is selected.

If customer is not selected, it shows 0 in the column.

I tried with if condition as follows:

Sum(if(DeliveryDate <= RequiredDate, DeliveredQty)

As of now, this is working.

Thanks Celambarasan and tresesco.

[CELAMBARASAN]

Hi,

Do u have any unique identifier to identify the record?

use that unique identifier in the place of UID

Sum({< [UID] = {"=DeliveryDate<=RequiredDate"} >} DeliveredQty)

Hope that helps

Celambarasan