topic Re: Set Analysis - Count(distinct not working in QlikView

Set Analysis - Count(distinct not working

Mon, 28 Jul 2014 07:40:12 GMT

Hi,

Goal : To calculated Promotional Order % to Total Orders

Expression:

Total orders = count(distinct(order_number))

Promotional Orders = count(DISTINCT(if(( promo_order_flag = 1),order_number,0)))

Dimension:

Year: year_no

(if I use: if ( promo_order_flag=1, year_no) Then I get correct count for Promotional orders, else I am getting unwanted blank rows

Month:

Day:

To calculate Promotional Order %

Now I changed expression of Total Orders = count( {$<promo_order_flag =, year_no=, day_no=, month_no=, redemption_type=>}distinct order_number)

But still I am getting same count.

If I remove promo_order_flag=1 from dimension (year_no): Then I get correct count, but getting unwanted rows

Please suggest where I am doing wrong! OR How to modify statements?

Many Thanks in Advance

- Deep

Re: Set Analysis - Count(distinct not working

Mon, 28 Jul 2014 07:41:26 GMT

Re: Set Analysis - Count(distinct not working

Mon, 28 Jul 2014 07:45:05 GMT

Hi Deepayan,

Can you please attach your qvw?

Thanks

Sabal

Re: Set Analysis - Count(distinct not working

MK_QSL — Mon, 28 Jul 2014 08:17:00 GMT

Go to your dimension tab... select order_number field and tick Suppress When Value is Null

Re: Set Analysis - Count(distinct not working

Mon, 28 Jul 2014 09:28:17 GMT

Hi Manish,

In case I suppress NULL Values, It suppress orders as well. (total Orders)

Regards

- Deep

Re: Set Analysis - Count(distinct not working

MK_QSL — Mon, 28 Jul 2014 09:32:10 GMT

Kindly provide sample apps...

Re: Re: Set Analysis - Count(distinct not working

Mon, 28 Jul 2014 09:32:44 GMT

Hi Sabal,

Please find attached document

Regards

- Deep

Re: Re: Set Analysis - Count(distinct not working

Mon, 28 Jul 2014 09:33:15 GMT

Document Attached

Re: Set Analysis - Count(distinct not working

MK_QSL — Mon, 28 Jul 2014 09:37:20 GMT

Instead of calculated dimension

=if(promo_order_flag=1,order_number)

try using order_number only..

if still now working, let me know what exactly the output you want..

Re: Re: Set Analysis - Count(distinct not working

Mon, 28 Jul 2014 09:47:47 GMT

As mentioned above in GOAL:

I am looking to calculate Promotional order % w.r.t Total Orders

Which will be: Promotional orders / Total Orders

where promotional orders identifier is promo_order_flag

Hence it will be

count(distinct(if(promo_order_flag=1,order_number)))

count(distinct(order_number))

Hence I am looking for Total Orders which should not deviate selecting any parameters. That is why I utried to use set analysis but failed

Re: Set Analysis - Count(distinct not working

MK_QSL — Mon, 28 Jul 2014 09:59:15 GMT

=COUNT({1<promo_order_flag = {1}>}DISTINCT order_number)/COUNT({1} DISTINCT order_number)

Re: Set Analysis - Count(distinct not working

Tue, 29 Jul 2014 05:39:54 GMT

It is not working:

It is same as before:

1. Either Blank Lines

2. OR if I use it Suppressing other ordres using promotional order flag (it is as same as before)

Could you please upload the test file I added?

Many Thanks

- Deep