39 Replies Latest reply: Oct 20, 2017 1:58 AM by Cameron Vine

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

• ###### 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.

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

The data model.

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

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

• ###### 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.

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

Here you go

• ###### 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.

• ###### 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

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

/

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

• ###### 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

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

But the image isn't what you wanted to get?

I also attached the qvf file back... did you look at that?

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

Sorry missed your file. When I opened up the file I don't get the 19.77% just blank fields, not only that the measure comes as a column not a measure.

Anyway, your representation above while showing the correct % for the SLA now has the Category at 100% instead of 19.22%, ie. 124/645.

All i need is to divide the Category or the Milestone count based on the row total which is so easy in excel like below..

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

Try this

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

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

Hi

Yours is more cleaner than mine and almost does the job.

If I click on Columns, Status and Show totals to on, the Totals only show 50% even though the percentages generate total 100%.

Cheers

Cameron

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

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

Thank you very much, I had briefly read about secondary dimensionality and only() today. Will need to do more study on this.

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

I would also suggest to upgrade your Qlik Sense to newer version, because this expression is much better than

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

/

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

the one we used above..... and this worked with Sept 2017 release for me at work... but did not work at home.... so to me it seems like a bug in one of the releases which might have resolved in Sept 2017....

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

I initially used the simple one but with no luck so will now look at upgrading. thanks again.

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

I have just done the upgrade and Sunny you are correct, the simple formula works now.

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

That's awesome....

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

Hey

I have just tried to apply the same principles from the last solution to another data set but I it will not calculate the % correctly in a pivot table although it works perfectly fine in a chart. I am using the below code but returns the results in the image. You can ignore the set statement.

=Sum({<[AccessTime.autoCalendar.Date]={">=01/05/2017"},[Category]={'Non Finance'},WeekDay={'Mon','Tue','Wed','Thu','Fri'},Message={'OK'}>}[Duration Seconds])/

Sum({<[AccessTime.autoCalendar.Date]={">=01/05/2017"},[Category]={'Non Finance'},WeekDay={'Mon','Tue','Wed','Thu','Fri'},Message={'OK'}>}

TOTAL <[AccessTime.autoCalendar.Month],[Category Level]> [Duration Seconds])

Any assistance would be much appreciated.

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

I believe you might need this

=Sum({<[AccessTime.autoCalendar.Date] = {">=01/05/2017"}, [Category] = {'Non Finance'}, WeekDay ={'Mon','Tue','Wed','Thu','Fri'}, Message = {'OK'}>} [Duration Seconds])/

Sum({<[AccessTime.autoCalendar.Date] = {">=01/05/2017"}, [Category] = {'Non Finance'}, WeekDay ={'Mon','Tue','Wed','Thu','Fri'}, Message={'OK'}>} TOTAL [Duration Seconds])

So, basically remove this from your expression

<[AccessTime.autoCalendar.Month], [Category Level]>

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

Did not like that, nothing changed.

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

It should have worked... would you be able to share a sample to look at this?

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

Hey, getting back to this now. I was extracting a sample data set to append for you and while compiling the App again, I found that the % worked correctly using Sum(TOTAL <[AccessTime.autoCalendar.Month]> [Duration Seconds]). The problem is that using the original data set gives me the wrong calculation. I can’t send you the original App as it is 24mg. Odd behaviour and at a loss as what to do now.

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

So can you explain what was incorrect about this image? I think I might have mis-understood the issue here

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

The problem with the above is that the chart works fine but when I use the same calcs in the pivot table I cant replicate the chart.

Let me get back to you later as I am in a workshop on and off this week. I have some more strange behaviour with a similar example.

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

Take your time

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

The first image works correctly in the chart but I cannot get the numbers correct in the pivot table which should be those in red. The chart uses a simple expression as opposed to a long expression (ie. Sum, Aggr, Only, NODISTINCT).

The second image I can get percentages to work on a Category Level basis in both the chart and the pivot table by using an adaptation of the long expression you initially provided. It still does not however calculate the total percentage to 100%.

In summary, I can get what I want if I use the long expression for both but still they would not calculate the total to 100%. Also, why doesn't a simple expression work?

I will attach a shorted QV file for you should you be interested.

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

Here is the qvf

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

Appears to be an issue with the auto calendar field that you are using... I created Month field in the script using Binary load and it seems to work with it

=Sum({<[AccessTime.autoCalendar.Date]={">=01/05/2017"},[Category]={'Non Finance'},WeekDay={'Mon','Tue','Wed','Thu','Fri'},Message={'OK'}>}[Duration Seconds])/

Sum({<[AccessTime.autoCalendar.Date]={">=01/05/2017"},[Category]={'Non Finance'},WeekDay={'Mon','Tue','Wed','Thu','Fri'},Message={'OK'}>}

TOTAL <Month> [Duration Seconds])

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

I am not as au fait with the the scripting as you so exactly what have you done given the script there is what I have as default?

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

Spoke to soon, figured it out Month(AccessTime) as Month.

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

Still odd behaviour though given that for pivot table I have to load a Month field but the chart will utilise the existing one from the default Qliksense script. Thanks again.

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

Further testing has found that loading the Month field into the chart does not work properly for Ave secs per user so I would have a different formula for the pivot table and the chart.

For the Count of Report I am still left with the long formula for the % chart as nothing seems to affect that.

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

Not sure I understand your concern... I changed the chart dimension from

=Month(Access) to just Month

and change the expression to this

=Sum({<[AccessTime.autoCalendar.Date]={">=01/05/2017"},[Category]={'Non Finance'},WeekDay={'Mon','Tue','Wed','Thu','Fri'},Message={'OK'}>}[Duration Seconds])/

Sum({<[AccessTime.autoCalendar.Date]={">=01/05/2017"},[Category]={'Non Finance'},WeekDay={'Mon','Tue','Wed','Thu','Fri'},Message={'OK'}>}

TOTAL <Month> [Duration Seconds])

Numbers are tieing up, what exactly is the problem?

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

So i just had a play and found that changing my Label from AccessTime.Month to Month changes my values correctly.

I did not think the Label field had any control over the expression.

I loaded the script slightly different to you but at the end of the days it all works. Thanks again.

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

That is great.... I am glad it all worked out in the end....

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

I am nearly there, now I just need some help on my if statement.

Expressions

• count({1<Status=-{'N/A'}>}Status)/aggr (NODISTINCT count({1<Status=-{'N/A'}>}Status), Category) gives me the percentage at the Category level and
• count({1}Status)/aggr(NODISTINCT count(Status),Category, Milestone) gives me the percentage at the SLA level.

My issue now is how do I get the if statement to return values based on the field/dimension as the below still only provides the first expression not the second in the column of values.

If(Match(Category,'Source','MaRRs'),

count({1<Status=-{'N/A'}>}Status)/aggr (NODISTINCT count({1<Status=-{'N/A'}>}Status), Category), //Category %

count({1}Status) / aggr(NODISTINCT count(Status),Category, Milestone)) //SLA %