# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor

## How to calculate the percentage of counts based on a sub total

Hi

This is very frustrating as a 30 second job in excel and a 2 minute job in Tableau has taken up a couple of hours of my time in Qliksense and I am still no closer to a solution.

All I want is the % of each type of SLA (Milestone), ie. Source and MaRRS (Reporting tool) that has breached an SLA based on the sub total, for example SLA 10 breach 17/124 = 19.77%. Refer attached

I have tried various different methods for just the denominator before I get to the percentage calc such as aggr and Total and Nodistinct but cannot get any closer to a solution, eg.

• aggr ( Sum ( TOTAL count({1<Status=-{'N/A'}>}Status)) Milestone, Category) = No Values
• count(TOTAL {1<Status=-{'N/A'}>}Status) = 1,290 which is wrong (124 + 521 + 199 +446)

How this cannot be out of the box functionality given we are meant to role this out within our organisation as a self service model is beyond me.

Anyway, any help would be greatly appreciated.

1 Solution

Accepted Solutions
MVP

## Re: How to calculate the percentage of counts based on a sub total

I think that this number will always be 100%, right... try this

If(SecondaryDimensionality() = 0, count({1<Status-={'N/A'}>}Status)/count({1<Status-={'N/A'}>}Status), count({1<Status-={'N/A'}>}Status)/Sum({1}Aggr(Only({1} Aggr(NODISTINCT Count({1<Status-={'N/A'}>}Status), Category, Milestone)), Category, Milestone, Status)))

39 Replies
MVP

## Re: How to calculate the percentage of counts based on a sub total

You should not need an Aggr() function here.

Correcting the syntax for excluding N/A:

Count(TOTAL {1<Status -= {'N/A'}>} Status)

Apart from that suggestion, its hard to say as you have not provided any information about the underlying data model and the image provides no insight.

Contributor

The data model.

Contributor

## Re: How to calculate the percentage of counts based on a sub total

Thanks for your prompt response, data model should now be attached.

Contributor

## Re: How to calculate the percentage of counts based on a sub total

I also tried this aggr (NODISTINCT count({1<Status-={'N/A'}>}Status), Category) = 645 but still does not help.

MVP

## Re: How to calculate the percentage of counts based on a sub total

I was thinking more of a qvw/qvf file already loaded with some representative data.

Contributor

## Re: How to calculate the percentage of counts based on a sub total

Here you go

Valued Contributor III

## Re: How to calculate the percentage of counts based on a sub total

Not sure try this in your denominator rather Aggr ,

count(TOTAL <Category> {1<Status-={'N/A'}>}Status)

MVP

## Re: How to calculate the percentage of counts based on a sub total

Is this what you want

Count({1<Status-={'N/A'}>}Status)

/

Count(TOTAL <Category, Milestone> {1<Status-={'N/A'}>}Status)

Contributor

## Re: How to calculate the percentage of counts based on a sub total

Hi

If I just use this to test the denominator Count(TOTAL <Category, Milestone> {1<Status-={'N/A'}>}Status), it does not return any values.

Cheers