Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

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

View solution in original post

40 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

The data model.

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Here you go

kkkumar82
Specialist III
Specialist III

Not sure try this in your denominator rather Aggr ,

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

sunny_talwar

Is this what you want

Capture.PNG

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

/

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

Anonymous
Not applicable
Author

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