15 Replies Latest reply: Sep 27, 2011 8:23 AM by Marc Livingston

# 'advanced' expression in a Gauge Chart

Hi Guys,

I'm still a newbie so forgive me for my ignorance.

I used to have a expression for my gauge chart that would calculate the performance of shipments ontime:

gauge expression:

Count({<Status={OnTime}>} Destination) / Count ( {<Status= >} Destination)

Since my data got a bit complex I had to remove the field "Status". Instead I would use an calculated dimension to see how many shipments are ontime or late.

This would be my chart expression.

=if(NetWorkDays(DEP,POD)<='LimitDays', 'OnTime', 'Late')

This works fine on a bar chart as it shows me ontime and late shipments.

I now want to messure the preformance, so I was wondering how can I translate the above gauge expression, without having a the status field?

The expression should be something like this

Count(Distinct Shipmentnumber that are ontime and divide them by all the shipments (ontime and late)

Dont know how to translate that to qlikview language :S

iSam

• ###### 'advanced' expression in a Gauge Chart

This should work:

This will give you on time

sum(if(NetWorkDays(DEP,POD)<='LimitDays', 1, 0))

This will give you Late

sum(if(NetWorkDays(DEP,POD)<='LimitDays', 0, 1))

I think this would work for the percent:

sum(if(NetWorkDays(DEP,POD)<='LimitDays', 1, 0)) / sum(if(NetWorkDays(DEP,POD)<='LimitDays', 1, 1))

• ###### 'advanced' expression in a Gauge Chart

Hi,

Thanks for your help! the expression you made, gives me a constant result of 100%. I've copied the expression to a bar chart, unfortunatelly it gives me the same results.

Do you have any other ideas?

iSam

• ###### 'advanced' expression in a Gauge Chart

try this:

sum(if(NetWorkDays(DEP,POD)<='LimitDays', 1, 0)) /Count (Destination)

if that does not work can you post a sample?

• ###### Re: 'advanced' expression in a Gauge Chart

Hi,

Unfortunately that didn’t work. Attached is a sample.

Hope u can help me.

iSam

• ###### Re: 'advanced' expression in a Gauge Chart

Ps: Basically I want qlikview to count (distinct) the STT unique shipping number that are ontime and divide them by all the shipments that are late and ontime. I just don't know how.

Cheers!

• ###### 'advanced' expression in a Gauge Chart

How do you determen if the shipment is on time? Which fields must I use for that?

• ###### Re: 'advanced' expression in a Gauge Chart

Hi Dennis,

You can use the field LimitDays.  E.g. I created a bar chart with 2 dimension: month and a calculated dimension ‘ if(networkdays(DEP,POD)<=’LimitDays’, ‘OnTime’, ‘Late’)  ‘ then I created the expression count (Distinct STT).  Based on that I get the all the shipments that are ontime and late.

I just don’t know how to translate it to a gauge chart since I’m not allowed to add dimesion in a gauge chart.

Does this info help?

Regards,

iSam

• ###### 'advanced' expression in a Gauge Chart

The field LimitDays is empty in (almost) all rows, in the QV-file you uploaded ...

• ###### 'advanced' expression in a Gauge Chart

I see. I think you have to change this in your load script:

Route:

Route, LimitDays

JFK | AMS, 7

YMQ | AMS, 7

];

to

Route:

Route, LimitDays

JFK|AMS, 7

YMQ|AMS, 7

];

• ###### Re: 'advanced' expression in a Gauge Chart

Hi Dennis,

Thanks for your response. I’ve attached my file.

Cheers!

iSam

• ###### Re: 'advanced' expression in a Gauge Chart

Try this expression:

sum(if(NetWorkDays(DEP,POD)<=LimitDays,1,0))/ Count(DEP)

Also, change the min on the gauge to 0, the result in the sample you gave returns 29% which will not show properly since your gauge is limited to 90-100 percent

• ###### Re: 'advanced' expression in a Gauge Chart

Hi Marc,

THANKS MAN! IT worked.  yeey J I am also curious,

sum(if(NetWorkDays(DEP,POD)<=LimitDays,1,0))  <ß doesn’t it sum  all the shipments that are ontime?

Cheers!

iSam

• ###### Re: 'advanced' expression in a Gauge Chart

Yes it is summing all the shipments that are on time. It should be the same as if you are counting only the ontime shipments.

I have used this method several times when I want to count a field based on certian criteria.

• ###### Re: 'advanced' expression in a Gauge Chart

Can it be around 30%?

See attached file.

I used marcsliving 's expression.

Succes.

Dennis.

• ###### Re: 'advanced' expression in a Gauge Chart

Thanks Dennis!! ‘t werkt!