Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET:
**REGISTER NOW**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Pivot table %

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

cbushey1

Creator III

2016-10-17
04:52 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Pivot table %

Hi there,

I am looking for a way for my percentage expression to only display a percentage based on the total that is displaying. I understand that typically you can use the TOTAL prefix in the expression to get the data across the whole database but I need to only display percentages across the 12 months that are displaying.

My Numerator expression is as follows:

=Sum(Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))

The two fields I am aggregating my CaseCounter over are the dimensions displayed in the pivot chart.

Suggestions?

802 Views

1 Solution

Accepted Solutions

sunny_talwar

MVP

2016-10-18
10:14 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

In the denominator or numerator or both?

**=Sum(Aggr(Sum({<Rolling12M_Flag = {1}, CancellationReason = {'*'}>} CaseCounter), Date_YM, CancellationReason))/Sum({<Rolling12M_Flag = {1}, CancellationReason = {'*'}>} TOTAL Aggr(Sum({<Rolling12M_Flag = {1}, CancellationReason = {'*'}>}CaseCounter), Date_YM, CancellationReason))**

7 Replies

sunny_talwar

MVP

2016-10-17
04:54 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

So I am guessing that you are using Rolling12M_Flag to display 12 month period? If that is true, you should be able to do just this:

**=Sum(Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))/****Sum(TOTAL Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))**

710 Views

cbushey1

Creator III

2016-10-18
08:58 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Sunny,

Thanks for the post, however I have tried that and it doesn't return the expected results. The problem is that the Total is ignoring my dimensions of Date_YM (i.e. October 2015...) and Cancellation reason and summing all values together. I know I have data outside of the last 12 months and unfortunately that is being included in the denominator.

I have also tried moving total to the inside Sum and many other variations with the Aggr to no avail.

Any other thoughts?

710 Views

sunny_talwar

MVP

2016-10-18
09:01 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

It might help to see a sample, but try these if you have not already done so

**=Sum(Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))/Sum( {<Rolling12M_Flag = {1}>} TOTAL Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))**

Also, are you using calculated dimension or dimension limits in your chart?

cbushey1

Creator III

2016-10-18
10:11 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I think this might have done it for me.

Is it possible to have this calculation ignore nulls?

I found I also have some nulls in my CancellationReason field.

710 Views

sunny_talwar

MVP

2016-10-18
10:14 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

In the denominator or numerator or both?

**=Sum(Aggr(Sum({<Rolling12M_Flag = {1}, CancellationReason = {'*'}>} CaseCounter), Date_YM, CancellationReason))/Sum({<Rolling12M_Flag = {1}, CancellationReason = {'*'}>} TOTAL Aggr(Sum({<Rolling12M_Flag = {1}, CancellationReason = {'*'}>}CaseCounter), Date_YM, CancellationReason))**

avinashelite

MVP

2016-10-18
10:17 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

may be

**Sum(Aggr(Sum({<Rolling12M_Flag = {1}, CancellationReason ={'*'}-{''}>}CaseCounter),Date_YM,CancellationReason))/Sum({<Rolling12M_Flag = {1},CancellationReason ={'*'}-{''}>}CaseCounter>} TOTAL Aggr(Sum({<Rolling12M_Flag = {1}>}CaseCounter),Date_YM,CancellationReason))**

710 Views

cbushey1

Creator III

2016-10-18
10:56 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks! That did the trick for me.