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

[Anonymous]

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.

[sunny_talwar]

That's awesome....

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous]

Did not like that, nothing changed.

[sunny_talwar]

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

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous]

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.

[sunny_talwar]

Take your time

[Anonymous]

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.