Set Analysis - Count(distinct not working

[#1]

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

[#2]

[#3]

Hi Deepayan,

Can you please attach your qvw?

Thanks

Sabal

[MK_QSL]

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

[#5]

Hi Manish,

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

Regards

- Deep

[MK_QSL]

Kindly provide sample apps...

[#7]

Hi Sabal,

Please find attached document

Regards

- Deep

[#8]

Document Attached

[MK_QSL]

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

[#10]

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