Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
guyvermeiren
Creator
Creator

Pivot table percentage of total column

Hello,

I would like to create a pivot table with where the represented result of the expression number is A) the absolute number and B) the percentage of the total of the column.

How to proceed ?

Thanks

Kind regards,

Guy

18 Replies
Not applicable

try this:

Expression 1: Sum(Amount)

Expression 2: Sum(Total Amount)
or sum(All Amount)

regards

guyvermeiren
Creator
Creator
Author

Hi Jesper,

Thanks

I understand the idea, and my total works, next problem is that I want the total for the year in column and not for all selected years (see example). How do I get this in my expression ??

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/8507.Example.pdf:550:0]

Kind regards,

Guy

Not applicable

I am having a similar problem here and was wondering if anyone has an answer? The sum(Total Amount) gets me a grand total of the lifetime of my data, but I'm trying to break it out year by year so I need each year's totals not a grand total of all years. Any suggestions?

Not applicable

After viewing this thread I think I'm on the right track. I have gotten to where I insert two different expressions.

I have percentages for the total over all 10 years but need it for each year. So each year's percentage add to 100 between the red and blue bars across the board.

My first expression I checked the Enable, Relative, Bar and Values on Data Points boxes with Total Mode being Sum of Rows. The second expression which was identical in definition only had Enable checked with a Total Mode of Sum of Rows.

johnw
Champion III
Champion III

The "total" qualifier can use a list of one or more fields. This isn't your specific requirement, but here's an example of the amount as a percentage of the total amount for the year:

sum(Amount)/sum(total <Year> Amount)

Your expression will probably be similar. Wherever you have total, just add the year. Now it looks like you don't actually have a Year field at this point. I recommend adding one instead of parsing the date in your charts.

Not applicable

I have a date field in my data (called offerDt) but it's broken out by day not year. I'm not sure if I would be able to just somehow use the year portion of the date value which is in yyyymmdd format in the table instead of adding a new date field? My expression to get the bar chart posted above is this:

sum(maturityFaceDollars)

where maturityFaceDollars is a dollar amount. I'm using that formula on both equations. I then tried sum(maturityFaceDollars)/sum(total <offerDt> maturityFaceDollars) but that didn't work either.

ashfaq_haseeb
Champion III
Champion III

Hi

get the year from your date field

by year (Date Field)

and follow what john suggests

Regards

ASHFAQ

Not applicable

Sorry I'm still pretty new to this but I tried inputting the expression but it didn't like it. Can you help see where I'm going wrong with this? I think it's because I'm bracketing the year value but if I don't then it won't accept the maturityFaceDollars field in the denominator...

sum(maturityFaceDollars)/sum(total <Year(offerDt) > maturityFaceDollars)



johnw
Champion III
Champion III

Create an OfferYear in your script:

year(offerDt) as OfferYear

Then use OfferYear. Something like this, probably:

sum(maturityFaceDollars)/sum(total <OfferYear> maturityFaceDollars)

Though honestly, it surprises me a bit that what you wrote didn't work. QlikView's usually pretty good about accepting "dimensions" that you build on the fly. I guess not in this case. Using a real field built in your script is a better approach regardless, though.

And typically, you'd actually build a calendar table rather than adding the year to whatever table your date field is in. I also prefer making my years and months be dates too, so that I can use date logic on them, but that's more personal preference than it being a better solution than using a year with a value like 2010. So something like this would be my normal approach:

OfferCalendar:
LOAD *
,date(yearstart(offerDt),'YYYY') as OfferYear
,date(monthstart(offerDt),'MMM YYYY') as OfferMonth
,date(weekstart(offerDt)) as OfferWeek
;
LOAD date(fieldvalue(offerDt,iterno())) as OfferDt
AUTOGENERATE 1
WHILE len(fieldvalue(offerDt,iterno()))
;

All of this is untested and subject to syntax and other errors.