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

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- Set analysis in a 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

Anonymous

Not applicable

2018-02-23
12:26 PM

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

Set analysis in a pivot table

I have the following pivot table: see attached.

Rows = Month, measures

Columns = Marketing type

Measures = multiple, including "% of Loans"

% of Loans is calculated as: that month's "Loans Generated" divided by that columns sum of "Loans Generated" across all the months.

I'm trying different versions of set analysis and total, but haven't been able to get the working formula yet. Can someone help? Thanks!

- Tags:
- qlik

4,320 Views

1 Solution

Accepted Solutions

Digvijay_Singh

MVP

2018-02-23
01:57 PM

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

I think what you are trying should show the correct for other months, can you share sample data/app, this below one is the other alternative which I didn't put properly in the last post -

Sum(S7_Ct)

/

Sum(Total<S7_ADType> S7_Ct)

Also do you see any difference if you make your measures as columns and type as rows?

4,124 Views

9 Replies

Anonymous

Not applicable

2018-02-23
12:53 PM

Author

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

The following formula works in coming up with the column totals: Sum( Aggr( sum(S7_Ct),S7_ADType) )

This will be the correct denominator in the "% of Loans" formula, so that looks like:

Sum(S7_Ct)

/

Sum(Aggr(sum(S7_Ct),S7_ADType))

However ... for testing, when I put the formula in the "% of Loans" measure in the pivot table, it ONLY puts the correct column total in the month of April 2017. For whatever reason, all the other months show up as $0. See attached for a screenshot of the formula in action.

4,124 Views

Digvijay_Singh

MVP

2018-02-23
01:34 PM

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

Can you check the result of this -

Sum(S7_Ct)

/

Sum(Total S7_Ct)

If possible please share sample data to look further.

4,123 Views

Anonymous

Not applicable

2018-02-23
01:38 PM

Author

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

Sum(Total S7_Ct) provides the total sum for all columns, not just the applicable column.

4,123 Views

Anonymous

Not applicable

2018-02-23
01:52 PM

Author

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

When I filter the pivot table on months, it seems to fill in the value for ONLY the month that comes first alphabetically.

For example, when I take out the month of April (which is the only month that had the number filled in) and only select Dec - Jan - Feb and July (randomly), only the month of December has the number completed. The rest is $0.

Why would only the 'first' (alphabetically) month show up with this calculation?

4,122 Views

Digvijay_Singh

MVP

2018-02-23
01:57 PM

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

I think what you are trying should show the correct for other months, can you share sample data/app, this below one is the other alternative which I didn't put properly in the last post -

Sum(S7_Ct)

/

Sum(Total<S7_ADType> S7_Ct)

Also do you see any difference if you make your measures as columns and type as rows?

4,125 Views

Digvijay_Singh

MVP

2018-02-23
02:02 PM

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

I think you need total by both Month and Type so, it should be like this -

Sum(Aggr(sum(S7_Ct),**Monthfield**,S7_ADType))

It was showing other months null as it calculates once for all months, I think if you would have added nodistinct in Aggr(), it would have shown same value in all the months.

4,123 Views

Anonymous

Not applicable

2018-02-23
04:13 PM

Author

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

For ease of troubleshooting, I'm only placing the denominator formula (as in, this should show the total number of old loans per column).

See screenshot "pivot_table": when I use Sum(Aggr(sum(S7_Ct),**Monthfield**,S7_ADType)), it basically shows the count per ADType and per Month.

However, it should do the sum without looking at Month.

See screenshot "pivot_table2": without the extra aggregation on Monthfield, it does calculate correctly ... but only for the first alphabetical month!?

4,123 Views

Anonymous

Not applicable

2018-02-23
05:23 PM

Author

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

See attached for a sample App. As you see, the column total only gets shown once. I need it to show for each row, as I would expect the formula to do.

4,123 Views

Anonymous

Not applicable

2018-02-26
01:18 PM

Author

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

Digvijay,

I was sure I tested this formula suggestion but it does work! Thank you!

Sum(TOTAL <Product> UnitSales)

Community Browser