topic Re: excluding zero values of other field from count distinct in Connectivity & Data Prep

excluding zero values of other field from count distinct

chan103148 — Fri, 10 Dec 2021 20:29:42 GMT

good day,

my fields are:

Traffic In

Tseq No

I want to compute for the conversion

Count(distinct [Tseq No])/Sum([Traffic In])

how can I disregard Tseq No that has corresponding Traffic In value equivalent to zero in the computation?

thank you.

Re: excluding zero values of other field from count distinct

anushree1 — Mon, 16 Dec 2019 06:12:52 GMT

Go to Add ons -> Data Handling->Uncheck Include Zero Values

Re: excluding zero values of other field from count distinct

chan103148 — Mon, 16 Dec 2019 06:20:01 GMT

its not working

i need to remove "Transaction count" with corresponding zero "Traffic In" in the formula.

Re: excluding zero values of other field from count distinct

anushree1 — Mon, 16 Dec 2019 06:23:48 GMT

can you share the sample file please.

Re: excluding zero values of other field from count distinct

chan103148 — Mon, 16 Dec 2019 06:30:36 GMT

here

Re: excluding zero values of other field from count distinct

anushree1 — Mon, 16 Dec 2019 06:39:07 GMT

Add an if condition on all the expressions like:

=if(sum(traffic)>0,Sum(transactions))

if(sum(traffic)>0,Sum([Ave price]))

and so on.. This should fix the issue

Re: excluding zero values of other field from count distinct

chan103148 — Mon, 16 Dec 2019 06:51:57 GMT

Count(distinct [Tseq No])/Sum([Traffic In])

*Tseq No is transactions

*Traffic In is traffic

how can i remove transaction count with corresponding zero traffic in the formula above.

Re: excluding zero values of other field from count distinct

chan103148 — Mon, 16 Dec 2019 06:54:10 GMT

there are dates that has transactions but no traffic count.

Re: excluding zero values of other field from count distinct

anushree1 — Mon, 16 Dec 2019 06:59:06 GMT

Use this:

if(sum(traffic)>0,Count(distinct transactions)/Sum(traffic))

Re: excluding zero values of other field from count distinct

chan103148 — Mon, 16 Dec 2019 07:00:27 GMT

not working 😞

Re: excluding zero values of other field from count distinct

anushree1 — Mon, 16 Dec 2019 07:04:15 GMT

works for me.

Please check the attached ,I have put jus 2 expressions , you can add similar expressions , also check the Data Handing Section it should be unchecked for Include Zero Vaues if you do not add an if condition for Sum(Traffic)

Re: excluding zero values of other field from count distinct

Kashyap_R — Mon, 16 Dec 2019 07:24:29 GMT

Try this expression for the date dimension

IF(traffic>0,Date)

Hope this helps

Thanks