Cumulative sum distint by product

[mee-qlik]

I would like to calculate the cumulative sum of a data set ( QVW attached ). I have already calculate a cumulative sum, progressive by product - date

but now my goal is to calculate cumulate sum by date for every product with dimension "date" in column ( see the excel grid ) and total

It's possible?

Thanks and Regards

Enrico

[olivierrobin]

hello

in the data you provide, product B has been sold with qty 20 on 01/02/2011

your graph shows 60 for that date, as if it was totalised with total of sells of product A

Normal ?

[sunny_talwar · Accepted solution]

Try this

If(SecondaryDimensionality() = 0, Sum(Amount), RangeSum(Before(Sum([Amount]), 0, ColumnNo())))

Where I made some script manipulation to generate missing data

Table:

LOAD *,

AutoNumber(Product&MyDate) as Key;

LOAD * INLINE [

    ID, Amount, MyDate, Product

    1, 10, 01/01/2011, A

    2, 10, 01/01/2011, A

    3, 10, 01/01/2011, A

    4, 10, 01/02/2011, B

    5, 10, 01/02/2011, B

    6, 10, 01/03/2011, B

    7, 10, 01/03/2011, B

    8, 10, 01/03/2011, B

    9, 10, 01/04/2011, B

    10, 10, 01/04/2011, B

    11, 10, 01/04/2011, B

    12, 10, 01/04/2011, B

    13, 10, 01/05/2011, B

    14, 10, 01/05/2011, B

    15, 10, 01/05/2011, A

];

TempTable:

LOAD DISTINCT Product

Resident  Table;

Join (TempTable)

LOAD Distinct MyDate

Resident Table;

Concatenate (Table)

LOAD *,

0 as Amount

Resident TempTable

Where not Exists(Key, AutoNumber(Product&MyDate));

DROP Table TempTable;

[mee-qlik]

Hi Robin,

yes it show 60 because i try to use Rangesum(above ....  to achieve my goal, but as you can see it is not correct ( for my goal)

[mee-qlik]

Thank you Sunny,

perfect solution!!!

Enrico