Bottom Up sum in my pivot table for the expression

[Anonymous]

Hi Experts,

I have below final table in Pivot table format,for the country Dimension I have value Expression for each stage.We need need bottom up sum for each country and need total at the extreme End

	EUROPE	EUROPE	India	India
	Date	Value	Date	Value
Stage1	9/8/2017	0	9/8/2017	0
Stage2	9/13/2017	0	9/13/2017	0
Stage3	9/18/2017	10	9/18/2017	0
Stage4	9/20/2017	9	9/20/2017	0
Stage5	9/21/2017	8	9/21/2017	0
Stage6	9/22/2017	6	9/22/2017	10
Stage7	9/22/2017	4	9/22/2017	9
Stage8	9/20/2017	2	9/20/2017	8
Stage9	9/22/2017	1	9/22/2017	6

Below output We are expecting,

For India from stage9 to Stage1 sum up

1=Stage9

3(1+2)=Stage8

7(1+2+4)=Stage7 etc.,,

and Total at the extreme end is 40 for Europe 31 for India

	EUROPE	EUROPE	India	India
	Date	Value	Date	Value
Stage1	9/8/2017	40	9/8/2017	31
Stage2	9/13/2017	40	9/13/2017	31
Stage3	9/18/2017	40	9/18/2017	31
Stage4	9/20/2017	30	9/20/2017	31
Stage5	9/21/2017	21	9/21/2017	31
Stage6	9/22/2017	13	9/22/2017	31
Stage7	9/22/2017	7	9/22/2017	21
Stage8	9/20/2017	3	9/20/2017	13
Stage9	9/22/2017	1	9/22/2017	6
Total		40		31

Could you please help me out to achieve the desired out put .

In-Advance

Thank you

[sunny_talwar]

May be this

Sum(TOTAL <Country> Value) - RangeSum(Above(Sum(Value), 1, RowNo()))

[sunny_talwar]

To get the totals, you can try this

If(Dimensionality() = 0, Sum(Value), Sum(TOTAL <Country> Value) - RangeSum(Above(Sum(Value), 1, RowNo())))

[Anonymous]

Tried above expression but no luck,may be I'm placing in a wrong way.

My current expression is

If(Dim2 = Date

WeekDay(Date) & ' ' &date(aggr(max(Date),Country,Stage)),

If(Dim2 = 'Value',

FirstSortedValue(Aggr(Sum(Value),Country,Stage,Dim2,Date),-Aggr(Max(Date),Country,Stage, Dim2,Date))

))

Dimensions :Stage,Country,Dim2

Aggregations Measures : Date,Value

please help me to sort out

[sunny_talwar]

Would you be able to share a sample qvw to look at this?

[Anonymous]

PFA

[sunny_talwar]

What is the expected output here?

[antoniotiman]

Hi Satya,

try this

RangeSum(Bottom(TOTAL  EUROPEValue,1,NoOfRows(TOTAL)- RowNo(Total)+1))

Regards,

Antonio

[Anonymous]

First of thanks for the prompt response.For the attached sample I have added note for the expected output.

[sunny_talwar · Accepted solution]

Seems like Antonio's solution works

If(Dim2 = 'Ende',

WeekDay(Ende) & ' ' &date(aggr(max(Ende),Supplier,Stage)),

If(Dim2 = 'Seiten',

RangeSum(Bottom(FirstSortedValue(Aggr(Sum(Seiten),Supplier,Stage,Dim2,Ende),-Aggr(Max(Ende),Supplier,Stage, Dim2,Ende)), 1, NoOfRows(TOTAL)- RowNo(Total)+1))

))