topic Re: How to create a cumulative percentage by month ? in QlikView

How to create a cumulative percentage by month ?

Wed, 06 Aug 2014 15:29:47 GMT

Hi everyone,

This is my first post on the qlik community

I try to have the percentage of buyers of a product vs a set of product ({<Product=p(ProductParent)>}) cumulative by month

if it was not cumulative, i could use this function. : =sum(buyer)/sum({<Product=p(ProductParent)>}buyer)

Exemple with only 2 Months

January : 3 buyers vs 5 in total

February : 7 buyers vs 15 in total

i would like to have a table with

January 60% (3/5)

February 50% ((3+7)/(5+15))

At the end, i should be able to select any range of Months.

Do you have an Idea how to proceed ?

For information:

I put Month variable in Dimension of my table but i don't think is a good idea.

I try to modify SetExpression but it seems it applies to all the table and not just on the Month category.

I can't create all possibilitites of cumulative Months

But maybe can I create a calculated dimension which represents cumulative Month ? ==> but i don't know how to proceed

Thank you for your answers.

Re: How to create a cumulative percentage by month ?

neo_lee — Wed, 06 Aug 2014 15:32:19 GMT

have you tried with Total in your expression ?

Re: How to create a cumulative percentage by month ?

MK_QSL — Wed, 06 Aug 2014 15:37:22 GMT

Provide sample data or apps please...!

Re: How to create a cumulative percentage by month ?

Wed, 06 Aug 2014 15:39:46 GMT

Yes I tried, but if i do that i will have the same result for both month which is

January 50% ((3+7)/(5+15))

February 50% ((3+7)/(5+15))

Re: How to create a cumulative percentage by month ?

Wed, 06 Aug 2014 15:40:34 GMT

example with the correct formula if someone need it

Re: How to create a cumulative percentage by month ?

neo_lee — Wed, 06 Aug 2014 15:41:06 GMT

yea, waiting for it

Re: How to create a cumulative percentage by month ?

aveeeeeee7en — Wed, 06 Aug 2014 16:04:46 GMT

Hi Benjamin

You can Achieve this result using something like this in your script:

Final:

Load *,

Num(Num(BuyersSum)/Num(TotalSum),'#,##0%') AS Percentage;

Load

Buyers,

Month,

Total,

alt(peek(BuyersSum),0) +Buyers as BuyersSum,

alt(peek(TotalSum),0) +Total as TotalSum;

Load * Inline [

Month, Buyers, Total

January, 3, 5

February, 7, 15

];

Copy the Script in your Edit Script and Run it. See the Result. It should be same as below. Also, see the Attachment.

Re: How to create a cumulative percentage by month ?

Wed, 06 Aug 2014 16:14:38 GMT

Yes it's a good idea.

But the problem is that the user shoud be able to select other range of date

Example : November 2013 to March 2014.

So i don't think it could be implemented.

Thanks for Helping.

Re: How to create a cumulative percentage by month ?

MK_QSL — Wed, 06 Aug 2014 18:29:26 GMT

Create a Straight Table

Dimension

Month

Expression as Below

RangeSum(Above(COUNT(Product),0,RowNo()))/RangeSum(Above(COUNT({<Product = , Month = {'>=$(=Min(Month))<=$(=Max(Month))'}>}Product),0,RowNo()))

Re: How to create a cumulative percentage by month ?

Thu, 07 Aug 2014 07:57:27 GMT

it look like it is working fine .

I try to figure how it work exactly and i see if it apllies to my study.

Thanks a lot.

I am doing some tests and it seems this part of the formula is not useful :

, Month = {'>=$(=Min(Month))<=$(=Max(Month))'}

and i think is normal, it will always return the same thing.

And why using this it the division and not in the numerator ?

Am i wrong ? is this part is mandatory ?

Still triyng to undersand the rest of the formula

Re: How to create a cumulative percentage by month ?

MK_QSL — Thu, 07 Aug 2014 08:00:09 GMT

Working, still helpful ?

Re: Re: How to create a cumulative percentage by month ?

Thu, 07 Aug 2014 08:25:17 GMT

Yep, Above function is just what I needed

Thank you a lot for you quick answer and have a nice day !