Announcements
cancel
Showing results for
Did you mean:
Not applicable

## QlikView Expression to multiply count with a field

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!

1 Solution

Accepted Solutions
MVP

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])

10 Replies
MVP

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])

Master

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

Not applicable
Author

Hi Andrew,

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

Not applicable
Author

Hi Sunny,

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

MVP

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])

Not applicable
Author

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

Not applicable
Author

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

MVP