Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

marjan_it
Contributor III

how to use count with if and aggr count

I have a table like this.

SELECT [Id]
,[VehivleTag]

,[ValidDate]
,[INorOUT]

FROM [Taxi].[dbo].[InputOutput]

 

with a date like this:

IdVehivleTagValidDateINorOUT
561632019-01-02 12:37:56.0001
1461632019-01-02 13:14:21.0000
2481632019-01-02 13:57:38.0001
5911632019-01-02 15:56:27.0000
7461642019-01-02 16:46:22.0001
22221642019-01-05 06:50:08.0000
25741642019-01-01 19:06:13.0000
25911642019-01-01 19:12:10.0001
30491642019-01-02 06:44:32.0000
35551642019-01-02 08:12:30.0001
38121642019-01-02 08:58:28.0000
38631642019-01-02 09:10:35.0001
43431642019-01-14 08:24:39.0001
46561652019-01-14 09:30:56.0000
47451652019-01-14 09:55:47.0001
48501652019-01-14 10:25:43.0000
48741652019-01-14 10:31:34.0001
49841652019-01-14 11:05:10.0000

 

I want to find the number of VehivleTag that count of Id if INorOUT=1 is less than 3.

in this sample data I want to get 2. means VehivleTag  in 163 and 165.

Labels (3)
5 Replies
Highlighted
MVP
MVP

Re: how to use count with if and aggr count

Try like:

Count(Distinct {<VehivleTag={"=Count({<INorOUT={1}>}VehivleTag)<3"}>} VehivleTag)

Re: how to use count with if and aggr count

If ID can repeat and you only want to check DISTINCT ID to be less than 3, then you can try this

Count(DISTINCT {<VehivleTag={"=Count(DISTINCT {<INorOUT={1}>} ID) < 3"}>} VehivleTag)
marjan_it
Contributor III

Re: how to use count with if and aggr count

No. It dose not work true.

marjan_it
Contributor III

Re: how to use count with if and aggr count

in total it works. but I want to show this in a chart. that dimension be date and expression be the number of taxi that count of VehivleTag is less than 3.
in chart it dose not work.

Re: how to use count with if and aggr count

Try this

Count(DISTINCT Aggr(If(Count(DISTINCT {<INorOUT={1}>} ID) < 3, VehicleTag), Date, VehicleTag))