sql calculation in qlikview

[#1]

Hi Experts,

i have the following calculation in sql :

SUM(count(distinct customerno))

how can i implement this in qlikview.

pls help

[MK_QSL]

SUM(count(distinct customerno))

Looks wrong... Nested Aggregation...!

[Kushal_Chawda]

In chart expression

count(distinct customerno)

[jonathandienst]

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.

[#5]

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

[sinanozdemir]

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.