Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.

1.JPG

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.

16 Replies
Not applicable
Author

Thanks:)

Not applicable
Author

Your feedbacks are highly appreciated.

Anonymous
Not applicable
Author

Hi,

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

See attached,

Jonathan

Not applicable
Author

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
Champion II
Champion II

in last expression use below

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

hope this helps

Sunil Chauhan
Anonymous
Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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
Not applicable
Author

"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)