Calculate % of a value and show value + remaining value

[amber2000]

Hi to you all,

I want to calculate a percentage of time. (I'm using Qlikview version 12)

PA_SecondsWorked (00:02:31) is 0,58 % of TR_Num_Minutes(07:15:00)

The calculation of the 0,58 % is already successful, now I also want to see the remaining 99,42%

To show those 2 values of % I want to use a bar chart.

I've attached my example with a small amount of data so it's easier to see what I want to accomplish, does anybody have a clue???

Monique

[Frank_Hartmann]

see attached

hope this helps !

[sunny_talwar · Accepted solution]

Or you can reference your columns here:

For bar chart

=1-Productive

For Straight table:

=1-Productive%

[Anonymous]

Other option, for twice graphs:

(Sum(Aggr(max(TR_Num_Minutes),Employee))-sum(PA_SecondsWorked)) /Sum(Aggr(max(TR_Num_Minutes),Employee))

Regards!!

[amber2000]

Hi Frank,

In the test case it works great but when I use it in my live data something goes wrong as you can see in the second attached file. I shouldn't get negative numbers.

[amber2000]

Hi Manuel,

Again in the live data it give's me a negative value: -50,75%

The first attach was simple test data.

[Anonymous]

Hi Monique, try this expression:

IF(Sum(Aggr(max(TR_Num_Minutes),Employee))>sum(PA_SecondsWorked),0,

(Sum(Aggr(max(TR_Num_Minutes),Employee))-sum(PA_SecondsWorked)) /Sum(Aggr(max(TR_Num_Minutes),Employee)))

This calculates remaining percentage only when PA_Seconds < Max(TR_Num_Minutes) by employee

Regards!!!

[amber2000]

Hi Sunny,

This is the fastest and most easy solution that works in both test and live data.

Thank you all for responding and taking the effort to help me.

Kind regards,

Monique

[Anonymous]

Hi Monique, on your productive expression you have a set analysis, that is not shown on your initial question.

I don't like to call previous columns, because any change on other columns can cause bad calculations on graphs.

With this it works:

(Sum(Aggr(max(TR_Num_Minutes),Employee_ID))-sum({$<[Art_OutHandleType] ={"*"}-{"PIECE"}>} PA_SecondsWorked)) /Sum(Aggr(max(TR_Num_Minutes),Employee_ID))

Regards!!

[sunny_talwar]

No problem at all