Count Percentage

[#1]

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

[sunny_talwar · Accepted solution]

You want this?

Expression used:

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

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

[#3]

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:

1. Order
2. Status
3. Reason

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

[sunny_talwar]

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)

[#5]

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