How to caculate relative percentage(get lastmonth value) in muti dimensions pivot table?

[#1]

Hello !

I want to caculate  relative percentage in muti dimensions pivot table.

So firstly I want to get LastMonth's summary.

I can use above function in ONLY one dimension's table.  When I have more than 1 dimesion, the above function is not working and I get the blank value.

I have thought that add a LastMonth field in script when load data. But I can't get the correct script yet. Or I can do in chart level?

Any body has some idea? 

Thank you very much.

[#2]

Thanks:)

[#3]

Your feedbacks are highly appreciated.

[Anonymous]

Hi,

Add 'TOTAL' keyword in to your expression, so it reads "=above(TOTAL sum( Value))"

See attached,

Jonathan

[#5]

Hi jonbroughavone,

Thank your for your reply.

Your qvw file is working only when the 1st dimension is YearMonth. In my real project, I have lots of dimensions and I permit users to adjust the order of dimensions. Sometimes the YearMonth is not in the 1st place in table.

So could you give me more suggestions?

[SunilChauhan]

in last expression use below

aggr(above( sum( Value)),YearMonth)

hope this helps

[Anonymous]

Not sure. You may need to add in some group by clauses alongside the total qualifier (using angled brackets <>).

I tried "=above(TOTAL sum(TOTAL <dimension1> Value))" on your example and it did generate some output for me, though it didn't look 100%.

Jonathan

[#8]

Hi  SK C ,

I tried your expression. It's not working all the time such as when YearMonth=200101 dimension1=1 dimension2=a.

I think:

when YearMonth=200101 dimension1=1 dimension2=a:   the LastMonth's value  should be 10;

when YearMonth=200101 dimension1=2 dimension2=a:   the LastMonth's value  should be  empty.

In other word, I want to get the value when the yearmonth=yearmonth -1 and the other dimessions are same.

[#9]

Hi  jonbroughavone,

I tried your expression. It's not working all the time such as when YearMonth=200101 dimension1=1 dimension2=a.

I think:

when YearMonth=200101 dimension1=1 dimension2=a:   the LastMonth's value  should be 10;

when YearMonth=200101 dimension1=2 dimension2=a:   the LastMonth's value  should be  empty.

when YearMonth=200104 dimension1=7 dimension2=c:   the LastMonth's value  should be  also empty.

In other word, I want to get the value when the yearmonth=yearmonth -1 and the other dimessions are same.

[Anonymous]

"In other word, I want to get the value when the yearmonth=yearmonth -1 and the other dimessions are same." .... sounds to me like like the above() method is overcomplicated and not suitable.

Perhaps you could instead have month (not YearMonth), dimension1 and dimension2 as dimensions, and current and previous year's value as two seperate expressions:

You would then only need to set up two variables for the current and prior year and test for these against a year field using either if statements or set analysis:

sum(if(Year=vCurrentYear,Value)) and sum(if(Year=vPriorYear,Value)), or

sum({$Year={'$(vCurrentYear)'}} Value) and sum({$<Year={'$(vPriorYear)'}>} Value)