Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
try a pivot table with
dimensions: Year,Month
expression: rangesum(above(total Sum([N° Customers]),1,rowno(total)))
Alex
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
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
Hi Mathieu,
Is it possible for you to attach a qvw ?? So that someone can work with that.........
🙂
Cheers
Sam
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
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.
If the qvd is that big load only 10 rows and send it to us...
I put my qvd in the previous post...