Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP 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

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
Author

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

Try :

As dimension: Location

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

Ivan_Bozov
Luminary
Luminary

Dimensions: Location, DepartStatus

Measure: Count(Trip)

Capture1.PNG

vizmind.eu
OmarBenSalem

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
Author

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

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.