topic Compare 2 fields in set analysis in QlikView

Compare 2 fields in set analysis

Mon, 26 Aug 2013 04:42:54 GMT

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.

Re: Compare 2 fields in set analysis

tresB — Mon, 26 Aug 2013 04:50:58 GMT

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?

Re: Compare 2 fields in set analysis

Mon, 26 Aug 2013 05:15:56 GMT

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.

Re: Compare 2 fields in set analysis

tresB — Mon, 26 Aug 2013 05:22:38 GMT

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.

Re: Compare 2 fields in set analysis

CELAMBARASAN — Mon, 26 Aug 2013 05:32:11 GMT

Hi,

Try with this

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

Hope that helps

Celambarasan

Re: Compare 2 fields in set analysis

Mon, 26 Aug 2013 06:39:45 GMT

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.

Re: Compare 2 fields in set analysis

CELAMBARASAN — Mon, 26 Aug 2013 06:53:28 GMT

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