topic Re: Cumulative Percentage in Straight Table in QlikView

Cumulative Percentage in Straight Table

Fri, 20 Nov 2015 16:59:39 GMT

Hi,

I need to get the cumulative percentage of the Module Assigned / Module Assigned (Completed) on a weekly basis. Something like I need to get the percentage of each row.

WeekEnd	Module Assigned	Completed	% Completion
6/19/2015	3	1	33.33%
6/26/2015	274 (271+3)	114	41.61%
9/25/2015	280 (274+6)	119	42.50%
10/30/2015	282	120	42.55%
11/6/2015	290	123	42.41%
11/13/2015	344	127	36.91%

Here's my expression for the Module Assigned and Completed (both in Full Accumulation already)

Module Assigned = count(Training)

Completed = count({$<Status = {'Completed'}>} Training)

Re: Cumulative Percentage in Straight Table

sinanozdemir — Fri, 20 Nov 2015 17:57:29 GMT

You can use the below expression:

Count({$<Status = {'Completed'}>} Training)/Count(Training)

Hope this helps.

Re: Cumulative Percentage in Straight Table

Fri, 20 Nov 2015 18:07:07 GMT

Hi Sinan,

I tried actually that expression but I'm not getting the correct results.

Re: Cumulative Percentage in Straight Table

sinanozdemir — Fri, 20 Nov 2015 18:09:50 GMT

Do you think you can post a sample qvw or your expression?

Re: Cumulative Percentage in Straight Table

nagarjuna_kotha — Fri, 20 Nov 2015 18:12:28 GMT

Hi Edward,

For %Cumulative you can also use below exp:

=Column(1) /Column(2)

Regards,

Nagarjuna

Re: Cumulative Percentage in Straight Table

nagarjuna_kotha — Fri, 20 Nov 2015 18:14:43 GMT

can you share sample data and your expected out put .

Regards,

Nagarjuna

Re: Cumulative Percentage in Straight Table

Fri, 20 Nov 2015 18:23:51 GMT

Hi Nagarjuna / Sinan,

Please see attached qvw file.

Data is under the Compass Training table.

Actually the data has no numerical data involved..Just the count of all the training I assigned per day and I want it actually to be grouped per week. Not sure how to do that in script.

Thanks,

Re: Cumulative Percentage in Straight Table

effinty2112 — Fri, 20 Nov 2015 18:36:55 GMT

Hi,

I'm not sure what a cumulative percentage means. The only sensible definition I can think of is the percentage ratio of two cumulative totals and that is what you have.

If I've misunderstood you I apologise.

Kind regards

Re: Cumulative Percentage in Straight Table

Fri, 20 Nov 2015 19:05:22 GMT

Hi Andrew,

Sorry if my question wasn't clear enough. But what I need to have is the percentage of each row.

This is the Actual Count each week.

This is the Full Accumulation

What I need to get is the percentage of each row. So for example, I should be getting 42.70% (120 / 281) for 9/25/2015, 42.75% (121 / 283).

Hope that makes sense.

Re: Cumulative Percentage in Straight Table

effinty2112 — Fri, 20 Nov 2015 19:48:12 GMT

Hi Edward,

I took your data and worked back to this table of non accumulated figures

WeekEnd	Module Assigned	Completed
6/19/2015	3	1
6/26/2015	271	113
9/25/2015	6	5
10/30/2015	2	1
11/06/2015	8	3
11/13/2015	54	4

You can make a straight table with the Weekend as dimension, all other columns are expressions. Instead of using the accumulation feature of the straight table use instead the expressions here to calculate the accumulation then the last column is the ratio of the preceding two expressed as a percentage.

WeekEnd	Module Assigned	Completed	RangeSum(Above([Module Assigned],0,RowNo()))	RangeSum(Above(Completed,0,RowNo()))	RangeSum(Above(Completed,0,RowNo())) / RangeSum(Above([Module Assigned],0,RowNo()))

6/19/2015	3	1	3	1	33.33%
6/26/2015	271	113	274	114	41.61%
9/25/2015	6	5	280	119	42.50%
10/30/2015	2	1	282	120	42.55%
11/06/2015	8	3	290	123	42.41%
11/13/2015	54	4	344	127	36.92%

hope this helps!

Re: Cumulative Percentage in Straight Table

sinanozdemir — Fri, 20 Nov 2015 20:29:14 GMT

I think the problem is here that Module Assigned and Completed columns are expressions with full accumulation. They are not dimensions.

Re: Cumulative Percentage in Straight Table

Mon, 23 Nov 2015 14:22:40 GMT

Thanks Andrew. I got it now.

This method will work if you have the count function from your script and then group by WeekEnd dimension and store it into QVD. Or from the QVW directly, use the AGGR function since the data was loaded per day. So we need to aggregate all the data into WeekEnd dimension.