Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Distinct Count

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

CustomerDateOrder ID
a1/1/2014123
a2/1/2014124
a6/1/2014154
b1/1/2014985
b2/1/2013452
b8/14/2013854

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)

7 Replies
deepakqlikview_123
Specialist
Specialist

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

Thanks

Not applicable
Author

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

JonnyPoole
Employee
Employee

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

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
michael_maeuser
Partner Ambassador
Partner Ambassador

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

JonnyPoole
Employee
Employee

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

michael_maeuser
Partner Ambassador
Partner Ambassador

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