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
The ferquency should be calculated based on the time dimensions(Date,Year....)
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)
Rather than use count distinct you can add a new table with a unique list of 'field name' and a numeric 1 value
1 as fieldnamecount
then just use sum(fieldnamecount). Instead of count(distinct fieldnamecount).
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...