Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Why u r not using count (distinct fieldname) as fieldname1, and use fieldname 1 for division at chart level.
Thanks
When i use count(Distinct fieldname) in the script it won't work based on the time selection
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).
Perhaps you can pre-calculate the number of customers per year in the script.
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
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
Right, sorry!! I didn´t read that you suggested to do this in a seperate table with only the customers and the count field.