topic Re: How can I create a % calculation based on a COUNT expression? in QlikView

How can I create a % calculation based on a COUNT expression?

Tue, 25 Feb 2014 14:42:51 GMT

I have a chart with a field 'Approval Status' and an expression 'COUNT(DISTINCT SKU_NUMBER)' which gives a count of DISTINCT sku_numbers for each value of approval_status. The field approval_status has 2 values 'Approved' and 'Unapproved'. So the count will give me the no. of SKUs for each of the 2 values of approval status. Now I need a % calculation which gives percentage of no. of distinct SKUs of the total no. of distinct SKUs for each value of approval status.

As an example, if 'Approved' has 60 SKU count and Unapproved has 40 SKU count, the % for approved will be 60% and for unapproved will be 40%.

How do I calcualte this percentage in a pivot chart?

Thank you,

Viral

Re: How can I create a % calculation based on a COUNT expression?

Nicole-Smith — Tue, 25 Feb 2014 14:46:17 GMT

Is Approval Status a dimension? If so, then your expression should be:

COUNT(DISTINCT SKU_NUMBER)/COUNT(TOTAL DISTINCT SKU_NUMBER)

Re: How can I create a % calculation based on a COUNT expression?

SunilChauhan — Tue, 25 Feb 2014 14:50:47 GMT

COUNT(DISTINCT SKU_NUMBER) / COUNT(all SKU_NUMBER)

try this hope this helps

Re: How can I create a % calculation based on a COUNT expression?

MK_QSL — Tue, 25 Feb 2014 14:52:57 GMT

Something like below...

Use this in Text Object

='Approved = '&NUM(COUNT({<[Approval Status] = {'Approved'}>}DISTINCT SKU_NUMBER)/COUNT(TOTAL DISTINCT SKU_NUMBER),'#,##0.00%') & CHR(10) &

'Unpproved = '&NUM(COUNT({<[Approval Status] = {'Unapproved'}>}DISTINCT SKU_NUMBER)/COUNT(TOTAL DISTINCT SKU_NUMBER),'#,##0.00%')

If you want to use in chart..

Approved

= COUNT({<[Approval Status] = {'Approved'}>}DISTINCT SKU_NUMBER)/COUNT(TOTAL DISTINCT SKU_NUMBER)

Unpproved

= COUNT({<[Approval Status] = {'Unapproved'}>}DISTINCT SKU_NUMBER)/COUNT(TOTAL DISTINCT SKU_NUMBER)

Re: How can I create a % calculation based on a COUNT expression?

Tue, 25 Feb 2014 16:55:12 GMT

This works fine.

Thank you.

Re: How can I create a % calculation based on a COUNT expression?

Nicole-Smith — Tue, 25 Feb 2014 16:58:52 GMT

Please mark correct and helpful answers so others can find solutions to similar problems