topic Count If in QlikView

Count If

danm84306 — Mon, 16 Nov 2015 20:14:10 GMT

Hello, I am trying to complete this calculation only counting data where the Location Column reads 'Remote'. Any ideas?

Sum(if(Type='T',[Time (Min)])/60/24)+Sum(if(Type='NT',[Time (Min)])/60/24))

/

((((Count(Distinct Date&Name))*480)/60/24)-Sum(if(Type='P',[Time (Min)])/60/24)-(((Count(Distinct Date&Name)-Sum(if(Type='P',Quantity)))*30)/60/24))

Re: Count If

sunny_talwar — Mon, 16 Nov 2015 20:48:59 GMT

May be this:

Sum({<Location = {'Remote'}>} if(Type='T',[Time (Min)])/60/24) + Sum({<Location = {'Remote'}>} if(Type='NT',[Time (Min)])/60/24))

/

((((Count({<Location = {'Remote'}>} Distinct Date&Name))*480)/60/24)-Sum({<Location = {'Remote'}>} if(Type='P',[Time (Min)])/60/24)-(((Count({<Location = {'Remote'}>} Distinct Date&Name)-Sum({<Location = {'Remote'}>} if(Type='P',Quantity)))*30)/60/24))

Re: Count If

PrashantSangle — Tue, 17 Nov 2015 04:37:14 GMT

Hi,

try every expression in set analysis.

Sum({<Location = {'Remote'},Type={'T','NT'}>}[Time (Min)])/60/24

(
(
(
   (
    Count({<Location = {'Remote'}>} Distinct Date&Name)
   )*480
)/60/24
)
-
Sum({<Location = {'Remote'},Type={'P'}>}[Time (Min)])/60/24
-
(
(
   (
    Count({<Location = {'Remote'}>} Distinct Date&Name)
    -
    Sum({<Location = {'Remote'},Type={'P'}>} Quantity)
   )*30
)/60/24
)
)

Regards

Re: Count If

Kushal_Chawda — Tue, 17 Nov 2015 06:37:36 GMT

=((Sum({<Type{'T'}>}[Time (Min)])/60/24)+(Sum({<Type{'NT'}>}[Time (Min)])/60/24))

(((Count(Distinct Date&Name)*480)/60/24)-(Sum({<Type={'P'}>}[Time (Min)])/60/24)-(((Count(Distinct Date&Name)-Sum({<Type={'P'}>}Quantity))*30)/60/24))