How to sum across dimension of a pivot table

[#1]

Hello,

I try to sum data across several dimension of my pivot table.

For the exemple :

3 dimensions :

[Year]

[Month]

[N° Customers]

Like :

If my database contains the followings value :

The customers do 150 € each month

I want to read the following result :

Where 2009-01 show me the sum([Montant ligne]) < '01/01/2009'

Where 2009-02 show me the sum([Montant ligne]) < '01/02/2009'

...

Somebody can help me?

Thank you

[#2]

Hi,

Your dimensions are:

Year, Month

Expression: Sum(if( Date < monthstart ( Date ), Montantligne, 0))

Note:

Ideally here Date refers to the column containing Dates.

Explanation of expression:

For the row where the date (Date column) is less than first day of month then take the respective Montant ligne value and finally do a sum on all such values obtained

Hope this helps,

Cheers

Sam

[#3]

Hi,

try a pivot table with

dimensions: Year,Month

expression: rangesum(above(total Sum([N° Customers]),1,rowno(total)))

Alex

[#4]

Hello Sam,

I have made many try with this formula.

But when I put a conditionnal date (like monthstart(Date)) in the formla, the result is always null

When i put a fix date like '01/01/2010' i have a result (Sum(total if( [Date de vente] < '01/01/2010' ), [Montant ligne], 0))

But a need to put these conditionnal date.

Thank you for your help.

Mathieu

[#5]

Hi Alex,

Thank you for you reply.

Your formula is good. But i have three dimension in my pivot table. The third dimension is N° Customers.

And I need to sum the goodsaling customers by customers with the same condition of date. So the formula with a rangesum doesnt work in this case.

Have you another magic formula for me?

Thank you

[#6]

Hi Mathieu,

Is it possible for you to attach a qvw ?? So that someone can work with that.........

🙂

Cheers

Sam

[#7]

Hi Sam,

my qvd is too big to e attached.

So i wish that somebody could find a solution without.

Thank a lot for help.

Mathieu

[#8]

Hi Sam,

I have reduce my qvd.

As you can see, I have problem with the 3e column : "Nb New Client Unique" with the following formula :

=count

(distinct if([Montant ligne]>0 and year([Date de création client])=[Année de vente] and sum(total distinct if([Date de vente]<=column(4),[Montant ligne]))<0,[Code client]))

My problem is on the sequence : sum(total distinct if([Date de vente]<=column(4),[Montant ligne]))<0

With those one, I try to check if on the last month the customers do not have a [Montant ligne] >0

Thank you.

[#9]

If the qvd is that big load only 10 rows and send it to us...

[#10]

I put my qvd in the previous post...