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

Hi,

I put the code sum(if(Year=vCurrentYear,Value)) to the script, and get the error below.

Error in expression:Nested aggregation not allowed

Please help...

Anonymous
Not applicable
Author

Sorry, I was suggesting you use those formulas as expressions in your chart/table. I wasn't thinking of any changes to the script (other than to include Month and Year as sperate fields alongside your YearMonth field.

Jonathan

jagan
Luminary Alumni
Luminary Alumni

Hi,

PFA, hope this is what you required.

=(Above(Total Sum(Value)))

Regards,

Jagan.

Anonymous
Not applicable
Author

See 12-Dec-2012 07:57 response Jagan. Having multiple dimensions makes that harder.

Jonathan

Not applicable
Author

I don't know why I can't see that post(12-Dec-2012 07:57 response Jagan). Could you pls tell me about the post? Thanks.

jagan
Luminary Alumni
Luminary Alumni

Hi,

PFA, hope this is what you required.

=(Above(Total Sum(Value)))

Regards,

Jagan.

Anonymous
Not applicable
Author

I was talking about one of your previous comment in this thread, where you said in response to my suggested above() function:

wormecota wrote:

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?