topic Excluding zero values in expression in Text Box in QlikView

Excluding zero values in expression in Text Box

123qlik123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I am not able to find the solution to count only 1's values in an expression and to exclude the zero values in expression.

Please find the below example. expression as below

Counts2 = count({<Year={$(=vYearSel)},Quarter={$(=vQuarterSel)},Month={$(=vMonthSel)},MonthYear={$(=vMonthYear)},

OptRegion={'$(=vRegionSel)'},OptSubRegion={'$(=vSubRegionSel)'},OptCountry={'$(=vCountrySel)'},

ProductName={'$(=vMCCPprod)'},Segment={'$(=vsegment)'},OptType={'Opt_In_vod'},LinkDataSource={'M'}>}DISTINCT AccountID)

I wanna count the number's that are 1's in a table, but not zero in the table in "Out Counts" and "Count2" only. For few cases the it's working fine

OptCountry	AccountID	Contact	Speciality	ID	Opt In Count	Opt_In%	Out counts	Out%	count2
Total						97.6%	10	2.4%	10
Japan	0019000001w114qAAA	a	Cardiology	PH_HCP_42171	0	0.0%	1	100.0%	0
Japan	0019000001f0IkaAAE	b	Internal Medicine	PH_HCP_41500	1	100.0%	0	0.0%	1
Japan	0019000000KUMmIAAX	c	Cardiology	PH_HCP_4129	1	100.0%	0	0.0%	1
Japan	0019000000KUM8CAAX	d	Cardiology	PH_HCP_36047	1	100.0%	0	0.0%	1
Japan	0019000000LiV6vAAF	e	Cardiology	PH_HCP_38109	1	100.0%	0	0.0%	1
Japan	0019000000KUNVXAA5	f	Cardiology	PH_HCP_11766	1	100.0%	0	0.0%	1
Japan	0019000000KUMcMAAX	g	Cardiology	PH_HCP_2424	1	100.0%	0	0.0%	1
Japan	0019000000NByo1AAD	h	Neurology	PH_HCP_11591	1	100.0%	0	0.0%	1
Japan	0019000000KUMQNAA5	i	Cardiology	PH_HCP_346	1	100.0%	0	0.0%	1
Japan	0019000000KUNkJAAX	J	Cardiology	PH_HCP_17617	1	100.0%	0	0.0%	1

kindly help. Thanks.!

Rerags,

MR.

Re: Excluding zero values in expression in Text Box

sibin_jacob — Tue, 31 Jul 2018 03:13:52 GMT

Hi,

Can you share the sample application?

Thanks,

Sibin

Re: Excluding zero values in expression in Text Box

Anil_Babu_Samineni — Tue, 31 Jul 2018 04:06:54 GMT

How about this?

If(

count({<Year={$(=vYearSel)},Quarter={$(=vQuarterSel)},Month={$(=vMonthSel)},MonthYear={$(=vMonthYear)},

OptRegion={'$(=vRegionSel)'},OptSubRegion={'$(=vSubRegionSel)'},OptCountry={'$(=vCountrySel)'},

ProductName={'$(=vMCCPprod)'},Segment={'$(=vsegment)'},OptType={'Opt_In_vod'},LinkDataSource={'M'}>}DISTINCT AccountID)<>0,

count({<Year={$(=vYearSel)},Quarter={$(=vQuarterSel)},Month={$(=vMonthSel)},MonthYear={$(=vMonthYear)},

OptRegion={'$(=vRegionSel)'},OptSubRegion={'$(=vSubRegionSel)'},OptCountry={'$(=vCountrySel)'},

ProductName={'$(=vMCCPprod)'},Segment={'$(=vsegment)'},OptType={'Opt_In_vod'},LinkDataSource={'M'}>}DISTINCT AccountID))

If(Column(1)<>0,Column(1))

If(MeasureLabel<>0,MeasureLabel)

Re: Excluding zero values in expression in Text Box

123qlik123 — Tue, 31 Jul 2018 05:19:04 GMT

Hi All,

Thanks for the reply.

I am not able to share the application, can you please suggest if my expression is giving any other errors.

Regards,
Ramana M