Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
i have the following calculation in sql :
SUM(count(distinct customerno))
how can i implement this in qlikview.
pls help
SUM(count(distinct customerno))
Looks wrong... Nested Aggregation...!
In chart expression
count(distinct customerno)
I think that the SUM is redundant in the above expression (the count will always return one value per sum).
How you implement in QV depends on the context (front end in a fact load, a dimension or mapping load, or in expressions in a text box, in a chart, in part of a set expression etc.
In principle, count (distinct customers) will work as is in QV and SQL. To nest aggregation functions in expressions, you need structure like:
Sum(Aggr(Count(Distinct customers), dim1, dim2, ...))
where dim1, dim2 are the fields you would be grouping by in SQL.
Provide more information if you need more specific help.
this is my actual sql query.
select SUM(CustDrops) as [Drops],avg(cast(CustDrops as decimal(8,2)))as [Avg Drops],
MAX(CustDrops) as [Max drops],MIN(CustDrops) as [Min Drops]
FROM (select branch,ordernumber,count(distinct customeraccoun) as CustDrops
from table l
group by branch,ordernumber) as tm
please help me to get this in qlikview
Maybe something like this:
LOAD
Sum(CustDrops) As Drops,
Avg(CustDrops) As [Avg Drops],
Max(CustDrops) As [Max Drops],
Min(CustDrops) As [Min Drops];
LOAD
branch,
ordernumber,
count(distinct customeraccoun) As CustDrops
FROM table_1
Group By branch, ordernumber;
Hope this helps.