topic Re: sql calculation in qlikview in QlikView

sql calculation in qlikview

Fri, 20 Nov 2020 18:02:30 GMT

Hi Experts,

i have the following calculation in sql :

SUM(count(distinct customerno))

how can i implement this in qlikview.

pls help

Re: sql calculation in qlikview

MK_QSL — Mon, 11 Jan 2016 13:46:09 GMT

SUM(count(distinct customerno))

Looks wrong... Nested Aggregation...!

Re: sql calculation in qlikview

Kushal_Chawda — Mon, 11 Jan 2016 13:46:20 GMT

In chart expression

count(distinct customerno)

Re: sql calculation in qlikview

jonathandienst — Mon, 11 Jan 2016 13:53:07 GMT

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.

Re: sql calculation in qlikview

Mon, 11 Jan 2016 14:15:25 GMT

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

Re: sql calculation in qlikview

sinanozdemir — Mon, 11 Jan 2016 14:29:33 GMT

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.