Pivot table percentage of total column

[guyvermeiren]

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

[#2]

try this:

Expression 1: Sum(Amount)

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

regards

[guyvermeiren]

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

[#4]

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?

[#5]

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]

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.

[#7]

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]

Hi

get the year from your date field

by year (Date Field)

and follow what john suggests

Regards

ASHFAQ

[#9]

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]

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.