topic Re: QlikView Expression to multiply count with a field in QlikView

QlikView Expression to multiply count with a field

Tue, 16 Aug 2016 09:35:04 GMT

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!

Re: QlikView Expression to multiply count with a field

sunny_talwar — Tue, 16 Aug 2016 09:38:35 GMT

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?

Re: QlikView Expression to multiply count with a field

effinty2112 — Tue, 16 Aug 2016 09:49:38 GMT

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

Re: QlikView Expression to multiply count with a field

Tue, 16 Aug 2016 10:46:52 GMT

Hi Andrew,

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

Re: QlikView Expression to multiply count with a field

Tue, 16 Aug 2016 11:05:57 GMT

Hi Sunny,

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

Re: QlikView Expression to multiply count with a field

sunny_talwar — Tue, 16 Aug 2016 11:07:12 GMT

Average or Min or Max?

Re: QlikView Expression to multiply count with a field

Tue, 16 Aug 2016 11:08:44 GMT

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

Re: QlikView Expression to multiply count with a field

Tue, 16 Aug 2016 11:10:37 GMT

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

Re: QlikView Expression to multiply count with a field

sunny_talwar — Tue, 16 Aug 2016 11:26:28 GMT

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

Re: QlikView Expression to multiply count with a field

Tue, 16 Aug 2016 11:36:15 GMT

No, this does not work.

Re: QlikView Expression to multiply count with a field

Anonymous — Tue, 16 Aug 2016 11:49:29 GMT

=Sum(aggr((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],YourDims))