topic Re: Set Analysis error in QlikView

Set Analysis error

didierodayo — Wed, 06 Jul 2016 01:28:23 GMT

Hello,

I can't get this set analysis expression to work. I have bar chart with following :

Dimension

Booking_ID

[BOOKING DATE]

expressions

COUNT({<PAYMENT_TYPE_CD={'20'}>}DISTINCT([BOOKING DATE]))

COUNT({<PAYMENT_TYPE_CD={'46'}>}DISTINCT([BOOKING DATE]))

it is working fine BUT Because of the high load we have decided to add WHERE the PAYMENT_TYPE_CD={'46'} ,SUM (BOOKING_INVOICE_AMOUNT>=1000 and RETURN only the booking_IDs that have a total payment_type_code>=1000.

I welcome your ideas.

Thanks

Re: Set Analysis error

Wed, 06 Jul 2016 01:40:01 GMT

count(distinct(if(PAYMENT_TYPE_CD='46' and SUM (BOOKING_INVOICE_AMOUNT>=1000,Booking_ID)))

Re: Set Analysis error

tamilarasu — Wed, 06 Jul 2016 02:24:14 GMT

Hi Didier,

May be this?

1. COUNT(DISTINCT {<PAYMENT_TYPE_CD={'20'},BOOKING_INVOICE_AMOUNT={"=Sum(BOOKING_INVOICE_AMOUNT)>=1000"}>} Booking_ID)

2. COUNT(DISTINCT {<PAYMENT_TYPE_CD={'46'},BOOKING_INVOICE_AMOUNT={"=Sum(BOOKING_INVOICE_AMOUNT)>=1000"}>} Booking_ID)

Re: Set Analysis error

didierodayo — Wed, 06 Jul 2016 02:46:42 GMT

Thanks Guys.

Re: Set Analysis error

maksim_senin — Wed, 06 Jul 2016 02:50:59 GMT

Hi Didier,

From performance point of view it's better to calculate this in the load script.

Also please give a look at P() and E() set analysis functions.

Best regards,

Maxim