Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

camjvine18
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

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

Capture.PNG

39 Replies
MVP
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.

camjvine18
Contributor

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

The data model.

camjvine18
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.

camjvine18
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
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.

camjvine18
Contributor

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

Here you go

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

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

Is this what you want

Capture.PNG

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

/

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

camjvine18
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

Community Browser