How to create a cumulative percentage by month ?

[#1]

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.

[neo_lee]

have you tried with Total in your expression ?

[MK_QSL]

Provide sample data or apps please...!

[#4]

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

[#5]

example with the correct formula if someone need it

[neo_lee]

yea, waiting for it

[aveeeeeee7en]

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.

[#8]

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.

[MK_QSL · Accepted solution]

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()))

[#10]

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