Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

nacera_abbas
New Contributor II

Cumulative Dimension

Hello,

I'm facing an issue about rangesum function in a Pivot table,

I calculate a cumulative amount like this

The expression:

if((Dimensionality())=1,

rangesum( sum(  aggr(   rangesum(   above(  Sum( {$<expr>} Amount)  ,0,rowno(TOTAL)  )  )  , Dimension 1  )  )

, sum (Amount)

I have 2 dimensions:

Dimension1 diplayed in rows and Dimension2 displayed in Columns.

This is how we want to display the pivot table:

Dimension1Dimension3Dimension4Dimension2Z1Z2Total
AA1102030
AA2A21134
AA2A22123
AA2A23246
AA2A24156
AA2Total41418
ATotal143448
BB1235
BTotal163753
C173855
D183957
E194059
FF1123
GTotal204262
Total204262

And for the moment I have this:

   

Dimension1Dimension3Dimension4Dimension2Z1Z2Total
AA1102030
AA2A21134
AA2A22123
AA2A23246
AA2A24156
AA2Total41418
ATotal34034
BB1235
BTotal53053
C55055
D57057
E59059
FF1303
GTotal62062
Total62062

Does anyone has any idea on how I could fix this issue?

Many thanks,

Nacera

Ce message a été modifié par : nacera abbas

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: Cumulative Dimension

Try this

If(Dimensionality() = 1,

Sum(Aggr(RangeSum(Above(Sum(Amount), 0, RowNo())), Month, Dimension1)),

Sum(Amount))

Capture.PNG

But because of some missing data, you will see that circle cells don't really match to what you want. To fix this, you will probably need to fix this in the script.

14 Replies
nacera_abbas
New Contributor II

Re: Cumulative Dimension

Does anyone can help on this?

MVP
MVP

Re: Cumulative Dimension

What is A1, A2 here? How you are you doing RangeSum on alphanumeric stuff like A1, A2? I am confused

nacera_abbas
New Contributor II

Re: Cumulative Dimension

The RangeSum is made on the Amount not the Dimension, A1 and A2 are value frome the Dimension3

nacera_abbas
New Contributor II

Re: Cumulative Dimension

I add a sample .qvw to be more clear.

As you can see below, for the first subtotal for Dimension1=A for Feb I have nothing and for Jan I have the Cumulative sum of Jan and Feb, I would like to see 9 for Feb and 17 for Jan and continue to cumulate at subtotoal for Dimension1=B Amount=A+B here as you can see 45= 26 for A and 19 for B

Example.PNG

Many thanks,

Nacera

MVP
MVP

Re: Cumulative Dimension

May be this

If(Dimensionality() = 1 and SecondaryDimensionality() = 0,

Sum(Aggr(RangeSum(Above(TOTAL Sum(Amount), 0, RowNo(TOTAL))), Dimension1)),

Sum(Amount))

nacera_abbas
New Contributor II

Re: Cumulative Dimension

It looks nice,

But I want the cumulative in B and C forJan and Fab as well, like below:

Dimension1Dimension2Dimension3MonthJanFebTotal
AA114519
AA2347
ATotal17926
BB19-9
BB2-1010
BTotal261945
CC1-33
CTotal262248
Total262248

Thanks,

Nacera

nacera_abbas
New Contributor II

Re: Cumulative Dimension

No one has an idea for this?

MVP
MVP

Re: Cumulative Dimension

Try this

If(Dimensionality() = 1,

Sum(Aggr(RangeSum(Above(Sum(Amount), 0, RowNo())), Month, Dimension1)),

Sum(Amount))

Capture.PNG

But because of some missing data, you will see that circle cells don't really match to what you want. To fix this, you will probably need to fix this in the script.

nacera_abbas
New Contributor II

Re: Cumulative Dimension

It worked, many thanks stalwar1‌!!!

Community Browser