How To Create Set Analysis To Count A Distinct Text Based On The Date Condition

[Dante_83]

Hello Experts,

I'm still learning! I'm trying to create a set analysis by counting a distinct text after a date range. Below is a dummy data to use as an example. I want to count column [CoDoc],'9C', after Oct/18/2021.

My expression  look something like below:

Count(Distinct{<[CoDoc]={'9C'}>{">=18/10/2021"}>[Codec])

I think my expression is wrong. May you please help? Thank you.

Serial#	Repair Id	Work Order	RShop	CoDoc	Model	Unit Price	Date Receive
F6784	HC2345677	5493066	92201	9C	R2D2-1	$2.62	18-Aug-2021
F6784	HC2345677	5424366	92201	9C	R2D2-1	$1.16	13-Aug-2021
F6784	HC2345677	5479698	92201	LSN	R2D2-1	$1.16	20-Aug-2021
F6784	HC2345677	5466456	92201	LSN	R2D2-1	$2,033.93	20-Aug-2021
F6784	HC2345677	5435114	92200	9C	R2D2-1	$2.85	18-Oct-2021
F6784	HC2345677	5486929	92200	9C	R2D2-1	$2.85	18-Oct-2021
F6784	HC2345677	5471089	92200	9C	R2D2-1	$2.85	18-Oct-2021
F6784	HC2345677	5528477	92200	6R	R2D2-1	$2.85	18-Oct-2021
F6784	HC2345677	5466420	92200	6R	R2D2-1	$2.85	18-Oct-2021
F6784	HC2345677	5415421	92200	6R	R2D2-1	$2.85	18-Oct-2021

[rubenmarin · Accepted solution]

Hi, the syntax would be:

Count(Distinct{<[CoDoc]={'9C'},[DateFieldToFilter]={">=18/10/2021"}>}[CoDoc])

[Dante_83]

Hi Rubenmarin,

It works great. Thank you!

[Dante_83]

Hello Rubenmarin,

I used the syntax you provided for '9C,' and the count of (1) is correct on my actual. But, the same syntax for another distinct ('6R') count also counts as 1, which should be 20. Could I be missing something? Any suggestions you may have to correct this will help? Thank you.

[rubenmarin]

Hi, now that you say... If you have a set analysis that filters only one value of CoDoc, and you count the distinct values of Codoc... if will always return 1 (or 0).

Try removing the "distinct". Or maybe you want to count another field, not the same CoDoc field that is used on set analysis.

[Dante_83]

Hello Rubenmarin,

Thank you for responding! When I remove the the distinct function, Count({<[CoDoc]={'9C'},[Date Receive]={">=14/9/2021"}>}[CoDoc]), it counted all of the 9C and not by the date criteria. I need the syntax to count  all the CoDoc after or before a certain date. Thank you. 

[rubenmarin · Accepted solution]

Hi, maybe the date filter wasn't working before but you didn't notice because of the distinct.

Working with dates can be tricky because of formats, you can try with:

Count({<[CoDoc]={'9C'},[Date Receive]={">=$(=Date('14/9/2021'))"}>}[CoDoc])

You can also check adding [Date Receive] to a table or ilter object. If it's right-aligned it's ok, if it's lef-aligned that means that it's being loaded as a text, not a dte, and comparing with >= won't work until it's converted to a date.

[Dante_83]

Hello Rubenmarin,

After weeks of trying to count CoDoc by date in Qlik Sense, you have answered it. Thank you so much for your help!