Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 ways modern analytics can help you take smarter action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shakeeb_mohammed
Contributor III
Contributor III

Expressions

Hi,

can someone assist me with the below please?

I have 3 fields in my app - One is called 'DepartureStatus'  the other one is called 'Trip' and the last one is called 'Location'

DepartureStatus field contains - OnTime,Late and Early and the Trip field contains Trip numbers and location has site departments.

What i want is show how many trips where on Time, early or late by location in a bar chart.

I'm having problems writing this expression - When I use sum(DepartureStatus) and use Location as Dimension i don't get a result  but i do when i use Count - but the figure is incorrect.

Can someone shed some light on this please?

11 Replies
OmarBenSalem
Partner
Partner

PLEASE, share mock data ; construct a table with these 3 columns as an input with 5 lines of data.

and explain the result u want to have; it would be MUCH EASIER for us to help

Thanks

shakeeb_mohammed
Contributor III
Contributor III

Trip

DepartStatusLocation
001On timeLondon
002On timeLondon
003LateLondon
004Early Birmingham
005LateBirmingham
006On timeBirmingham

Thanks for your reply,

Please see above example data - all i want is to show how many Trips where On Time, Late or Early by Location in a bar chart.

OmarBenSalem
Partner
Partner

Try :

As dimension: Location

As measure: count(distinct {<DepartStatus={'On time'}>} Trip)

Ivan_Bozov
Luminary
Luminary

Dimensions: Location, DepartStatus

Measure: Count(Trip)

Capture1.PNG

OmarBenSalem
Partner
Partner

Thought he was only aiming to see the ontime; I've misread

Ivan's answer is the correct way to do it Shakeeb

shakeeb_mohammed
Contributor III
Contributor III

Thank you guys ! both worked apart from I am getting a different figure in my app!

I have a total of 41 Distinct trips

But my graph is showing 29 Lates, 28 On time and 1 Early??

I should have been more accurate with my data source - I will have duplicate lines of trip number hence me using Distinct Trip.

timpoismans
Specialist
Specialist

How can the trip number have duplicate lines?

Do you have more than one DepartureStatus per trip number? If so, you shouldn't use Count(Distinct Trip) but just Count(Trip).

OmarBenSalem
Partner
Partner

u should usually count(distinct) instead of count to avoid duplications

timpoismans
Specialist
Specialist

If there are multiple DepartureStatuses per trip number, it won't help him to do a count(distinct) though.