topic Re: Count Percentage in QlikView

Count Percentage

Fri, 05 Aug 2016 00:17:30 GMT

Hi Guys,

I have a scenario where Where an Order can go through different statuses during it's life time and each status has a reason.

so as an example.. Order can have status 'On Hold' for reason 'Awaiting Payment' and just like this there are many reasons for being on Hold for being cancelled

I want to calculate the most popular reasons for Order being on Hold or being Cancelled

I have tried following formula in the expression but it is not giving desired = count(OrderId) / count(total(OrderId))

Sample QVW attached

Re: Count Percentage

sunny_talwar — Fri, 05 Aug 2016 00:31:05 GMT

You want this?

Expression used:

Count({<HoldReason = {'*'}>}OrderId)/Count(TOTAL {<HoldReason = {'*'}>} OrderId)

Count({<CancellationReason = {'*'}>}OrderId)/Count(TOTAL {<CancellationReason = {'*'}>} OrderId)

Re: Count Percentage

Fri, 05 Aug 2016 00:42:19 GMT

Try these changes to your expression:

count(OrderId) / count({$<HoldReason={'*'}>} total(OrderId))

count(OrderId) / count({$<CancellationReason={'*'}>} TOTAL (OrderId))

See attached.

They produce this:

Consider using a simpler data-model, only these columns:

Order
Status
Reason

That data-model would allow for clearer and/or simpler solution(s)..

Re: Count Percentage

sunny_talwar — Fri, 05 Aug 2016 00:46:32 GMT

Your simpler model made me think that why are we even using OrderId here, why don't we use this:

Count(HoldReason)/Count(TOTAL HoldReason)

and

Count(CancellationReason)/Count(TOTAL CancellationReason)

Re: Count Percentage

Fri, 05 Aug 2016 17:51:40 GMT

You're right! Of course! But this doesn't even require a simpler model, it works with original model...

The principle is that COUNT() will not COUNT null values, right?

Good idea; best answer so far