
Re: Set analysis in a pivot table
Jorn Vangoidtsenhoven Feb 23, 2018 1:06 PM (in response to Jorn Vangoidtsenhoven)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.

pivot_table.jpg 51.2 K

Re: Set analysis in a pivot table
Digvijay Singh Feb 23, 2018 1:34 PM (in response to Jorn Vangoidtsenhoven)Can you check the result of this 
Sum(S7_Ct)
/
Sum(Total S7_Ct)
If possible please share sample data to look further.

Re: Set analysis in a pivot table
Jorn Vangoidtsenhoven Feb 23, 2018 1:38 PM (in response to Digvijay Singh )Sum(Total S7_Ct) provides the total sum for all columns, not just the applicable column.

Re: Set analysis in a pivot table
Jorn Vangoidtsenhoven Feb 23, 2018 1:52 PM (in response to Jorn Vangoidtsenhoven)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?

Re: Set analysis in a pivot table
Digvijay Singh Feb 23, 2018 1:57 PM (in response to Jorn Vangoidtsenhoven)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?

Re: Set analysis in a pivot table
Digvijay Singh Feb 23, 2018 2:02 PM (in response to Digvijay Singh )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.

Re: Set analysis in a pivot table
Jorn Vangoidtsenhoven Feb 23, 2018 4:16 PM (in response to Digvijay Singh )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!?

pivot_table2.JPG 34.6 K

pivot_table.jpg 42.3 K


Re: Set analysis in a pivot table
Jorn Vangoidtsenhoven Feb 23, 2018 5:23 PM (in response to Digvijay Singh )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.

Test_AggrSum.qvf 176.0 K



Re: Set analysis in a pivot table
Jorn Vangoidtsenhoven Feb 26, 2018 1:18 PM (in response to Digvijay Singh )Digvijay,
I was sure I tested this formula suggestion but it does work! Thank you!
Sum(TOTAL <Product> UnitSales)



