topic Re: Distinct Count in QlikView

Distinct Count

Thu, 04 Sep 2014 12:14:21 GMT

HI All,

I Need a help!!!!!!!!

I am having an table and i need to create an frequency based on the Customer and the Order.

I have used the formula of Count(Order ID)/Count(Distinct Customer).

But when i use Distinct it consumes more memory and my Qvs is getting disconnected.

As there is an millions of records.

Consider the below example for your reference

Customer	Date	Order ID
a	1/1/2014	123
a	2/1/2014	124
a	6/1/2014	154
b	1/1/2014	985
b	2/1/2013	452
b	8/14/2013	854

The ferquency should be calculated based on the time dimensions(Date,Year....)

Expected result

If 2014 is selected i need to show 4/2=2i.e(count(Order ID)/ count( Distinct Customer)

if 2013 is selected 2/1=2

if jan to14 is selectd 2/2=1

Let me know if there is an way to achive this with out using an Count(Distinct)

Re: Distinct Count

deepakqlikview_123 — Thu, 04 Sep 2014 12:18:20 GMT

Why u r not using count (distinct fieldname) as fieldname1, and use fieldname 1 for division at chart level.

Thanks

Re: Distinct Count

Thu, 04 Sep 2014 12:20:30 GMT

When i use count(Distinct fieldname) in the script it won't work based on the time selection

Re: Distinct Count

JonnyPoole — Thu, 04 Sep 2014 12:23:35 GMT

Rather than use count distinct you can add a new table with a unique list of 'field name' and a numeric 1 value

load distinct

fieldname,

1 as fieldnamecount

resident <table>;

then just use sum(fieldnamecount). Instead of count(distinct fieldnamecount).

Re: Distinct Count

Gysbert_Wassenaar — Thu, 04 Sep 2014 12:29:06 GMT

Perhaps you can pre-calculate the number of customers per year in the script.

Re: Distinct Count

michael_maeuser — Thu, 04 Sep 2014 12:34:17 GMT

Jonathan that won´t work due to the time filters.

Sampath: I just think about a solution. What definately helps a little bit is to create a new field e.g. Counter with always 1 as value. so your expressio will look like

sum(counter)/ count( Distinct Customer)

that should also help a little, but of course the distinct is a single threaded function and does only use one core.

i try to also find a solution for this

Re: Distinct Count

JonnyPoole — Thu, 04 Sep 2014 13:11:43 GMT

Not sure why it wouldn't . Time filters will filter the main table and will filter to only the required customers in the lookup table through the associative model and should result in accurate sums,

However, consider this post (myth) about count distinct. Its a great post that also lays out alternatives to count distincts...

http://community.qlik.com/blogs/qlikviewdesignblog/2013/10/22/a-myth-about-countdistinct

Re: Distinct Count

michael_maeuser — Thu, 04 Sep 2014 13:40:39 GMT

Right, sorry!! I didn´t read that you suggested to do this in a seperate table with only the customers and the count field.