Dividing values

[steve_br]

Hello!

I have a data file about production and two of the columns are time (minutes) and cost. It also contains info about plants, workers, etc. I would like to include a KPI that shows the cost per minute and changes when I select different plants on my visualization. So I need to divide cost by time but I have no idea how to include this as a measure. The values are non-integer. Can anybody help me out here please?

Thanks!

PS: I am only using Qlik Sense for two days now, so please keep in mind that I'm a total noob.

[swuehl]

One of the first thing to learn in Qlik is how to handle date and time values.

Please have a look at

Data Types in QlikView

Get the Dates Right

Why don’t my dates work?

If you are looking at time always in terms of minutes, you could interprete / transform your time field so that it shows a numeric value in minutes. If it's currently a text value like '05:30', maybe like

LOAD

     Interval#(TIMEFIELD, 'mm:ss')*24*60 as TimeInMinutes,

     ...

FROM ...;

Then you can use a KPI expression like

=Sum(COSTFIELD) / Sum(TimeInMinutes)

to get the average cost per minute

[steve_br]

Thank you for your answer!

The time is shown in minutes so i don't need this LOAD expression, right? Just for future reference, where exactly should It be entered. And is there an easier way adjust settings like this, e.g. something like the format cells feature in Excel?

Now back to my question. When I enter =Sum(Cost) / Sum(Time) as an expression in the KPI filed Add measure, I get an error saying: Error in expression: ')' expected. Don't know what is wrong.

btw. the data looks like this:

Plant | Worker | Time      | Cost

Pl.1   | W1       | 150        | 77,8

Pl.1   | W2       | 176        | 83,2

Pl.1   | W3       | 147        | 82,2 

Pl.2   | W4       | 119        | 90,6

...............

Pl.35  | W453  | 155        | 67,8

Thanks again and sorry for the lame questions.

[swuehl · Accepted solution]

Not sure what the issue is.

Attached a sample QVF with your sample table.

If this is not helping you, can you post your sample app?

[steve_br]

Awesome, it worked now! Thank you very much for the sample file!

Could you please also answer my question about this LOAD expression and if there is an easier way to change the time values in Qlik Sense. I am pretty sure that I'll need it in the future. Thanks!

[swuehl]

You need to understand how QlikView stores date and time values. See my previously posted links.

You would interpret source data that is not automatically read in as date / time using interpretation functions like Date#() , Timestamp#() or Time#() in the LOAD script.

You can set default formats in the LOAD script that helps QV to interpret your source data correctly.

[steve_br]

Okay, thank you so much!