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
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
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
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

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.

Partner
Partner

Try :

As dimension: Location

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

Luminary
Luminary

Dimensions: Location, DepartStatus

Measure: Count(Trip)

Capture1.PNG

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

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.

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).

Partner
Partner

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

Specialist
Specialist

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