QlikView Expression to multiply count with a field

[#1]

Hello Friends,

I have been calculating using a qlikview expression, however its a count function  in combination with a field .

=(count( {$<Success_Factor >}[Ticket #]) - count(  {$< Success_Factor ={'Success'} >}[Ticket #]))/(count( {$<Success_Factor = {'Success'} >}[Ticket #]) +count( {$<Success_Factor >}[Ticket #]) - count( {$<Success_Factor = {'Success'} >}[Ticket #]))* [Ship Out Performance on time]

I would like to multiply the first part of calculation with the  Ship Out Performance on time.

I do get a null value because I do not know the correct way to calculate.

Any clues to calculate this expression in the best possible way ?

Many Thanks!

[sunny_talwar]

Not sure how your data is, but do you have multiple different values for Ship Out Performance on time per dimension? May be use Sum, Avg, Min, Max?

=(count( {$<Success_Factor >}[Ticket #]) - count(  {$< Success_Factor ={'Success'} >}[Ticket #]))/(count( {$<Success_Factor = {'Success'} >}[Ticket #]) +count( {$<Success_Factor >}[Ticket #]) - count( {$<Success_Factor = {'Success'} >}[Ticket #])) * Sum([Ship Out Performance on time])

[effinty2112]

Hello,

I think you can simplify your expression

=(count( {$<Success_Factor >}[Ticket #]) - count(  {$< Success_Factor ={'Success'} >}[Ticket #]))

/

(count( {$<Success_Factor = {'Success'} >}[Ticket #]) +count( {$<Success_Factor >}[Ticket #])

- count( {$<Success_Factor = {'Success'} >}[Ticket #]) )* [Ship Out Performance on time]

equals

=(count( {$<Success_Factor >}[Ticket #]) - count(  {$< Success_Factor ={'Success'} >}[Ticket #]))

/

count( {$<Success_Factor >}[Ticket #])* [Ship Out Performance on time]

equals

[Ship Out Performance on time] *count(  {$< Success_Factor -={'Success'} >}[Ticket #]) /count([Ticket #])

Cheers

Andrew

[#4]

Hi Andrew,

Thanks but it is not very clear to me...

[#5]

Hi Sunny,

I can't use sum because the field Ship Out Performance on time is expressed in percentages.

[sunny_talwar · Accepted solution]

Average or Min or Max?

=(count( {$<Success_Factor >}[Ticket #]) - count(  {$< Success_Factor ={'Success'} >}[Ticket #]))/(count( {$<Success_Factor = {'Success'} >}[Ticket #]) +count( {$<Success_Factor >}[Ticket #]) - count( {$<Success_Factor = {'Success'} >}[Ticket #])) * Avg([Ship Out Performance on time])

or

=(count( {$<Success_Factor >}[Ticket #]) - count(  {$< Success_Factor ={'Success'} >}[Ticket #]))/(count( {$<Success_Factor = {'Success'} >}[Ticket #]) +count( {$<Success_Factor >}[Ticket #]) - count( {$<Success_Factor = {'Success'} >}[Ticket #])) * Min([Ship Out Performance on time])

or

=(count( {$<Success_Factor >}[Ticket #]) - count(  {$< Success_Factor ={'Success'} >}[Ticket #]))/(count( {$<Success_Factor = {'Success'} >}[Ticket #]) +count( {$<Success_Factor >}[Ticket #]) - count( {$<Success_Factor = {'Success'} >}[Ticket #])) * Max([Ship Out Performance on time])

[#7]

Yes there are multiple values for Ship Out Performance on time per dimension so using sum would not help

[#8]

This would not help as each row would have to be multiplied with different value of ship out performance on time.

[sunny_talwar]

How about this?

=(Count({$<Success_Factor >}[Ticket #] * [Ship Out Performance on time]) - Count({$< Success_Factor ={'Success'} >}[Ticket #] * [Ship Out Performance on time]))/(Count({$<Success_Factor = {'Success'}>}[Ticket #] * [Ship Out Performance on time]) + Count({$<Success_Factor >}[Ticket #] * [Ship Out Performance on time]) - Count({$<Success_Factor = {'Success'} >}[Ticket #] * [Ship Out Performance on time]))

[#10]

No, this does not work.