topic Sum formula is not working correctly in pivot table in App Development
https://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830285#M68425
<P>I have the following formula for Adjusted Yield in my pivot table:</P><P>If(Sum(WalkbyQty) > 0, (Sum(EU_RecQty) / Sum(Acres)) + (Sum(WalkbyQty) / Sum(Acres)),(Sum(EU_RecQty) / Sum(Acres)))</P><P>In my rows, I have five dimensions for drill down. The Adjusted Yield formula does not calculate correctly at the top two levels; however, if I drill down further, then the formula appears to work correctly. I have a feeling this formula might require more refinement such as with the Aggr function, but I would like to get some feedback and suggestions please. Thank you.</P><P>Example of incorrect calculation for Adj Yield:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="mikegrattan_0-1629494552685.png" style="width: 400px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/60540iC873AB929A5B2FD5/image-size/medium?v=v2&px=400" role="button" title="mikegrattan_0-1629494552685.png" alt="mikegrattan_0-1629494552685.png" /></span></P><P>Drill down one level on G&H Farms and all numbers suddenly are calculated correctly:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="mikegrattan_1-1629494600086.png" style="width: 400px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/60541i417E7854749606FF/image-size/medium?v=v2&px=400" role="button" title="mikegrattan_1-1629494600086.png" alt="mikegrattan_1-1629494600086.png" /></span></P><P> </P><P> </P>Fri, 20 Aug 2021 21:24:02 GMTmikegrattan2021-08-20T21:24:02ZSum formula is not working correctly in pivot table
https://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830285#M68425
<P>I have the following formula for Adjusted Yield in my pivot table:</P><P>If(Sum(WalkbyQty) > 0, (Sum(EU_RecQty) / Sum(Acres)) + (Sum(WalkbyQty) / Sum(Acres)),(Sum(EU_RecQty) / Sum(Acres)))</P><P>In my rows, I have five dimensions for drill down. The Adjusted Yield formula does not calculate correctly at the top two levels; however, if I drill down further, then the formula appears to work correctly. I have a feeling this formula might require more refinement such as with the Aggr function, but I would like to get some feedback and suggestions please. Thank you.</P><P>Example of incorrect calculation for Adj Yield:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="mikegrattan_0-1629494552685.png" style="width: 400px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/60540iC873AB929A5B2FD5/image-size/medium?v=v2&px=400" role="button" title="mikegrattan_0-1629494552685.png" alt="mikegrattan_0-1629494552685.png" /></span></P><P>Drill down one level on G&H Farms and all numbers suddenly are calculated correctly:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="mikegrattan_1-1629494600086.png" style="width: 400px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/60541i417E7854749606FF/image-size/medium?v=v2&px=400" role="button" title="mikegrattan_1-1629494600086.png" alt="mikegrattan_1-1629494600086.png" /></span></P><P> </P><P> </P>Fri, 20 Aug 2021 21:24:02 GMThttps://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830285#M68425mikegrattan2021-08-20T21:24:02ZRe: Sum formula is not working correctly in pivot table
https://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830291#M68426
<P><LI-USER uid="49220"></LI-USER> one way is to use dimensionality to aggregate your measure at each level of dimensions using aggr. </P><P> </P><LI-CODE lang="javascript">=Pick(dimensionality(),
sum(aggr(If(Sum(WalkbyQty) > 0, (Sum(EU_RecQty) / Sum(Acres)) + (Sum(WalkbyQty) / Sum(Acres)),(Sum(EU_RecQty) / Sum(Acres))), Location)),
sum(aggr(If(Sum(WalkbyQty) > 0, (Sum(EU_RecQty) / Sum(Acres)) + (Sum(WalkbyQty) / Sum(Acres)),(Sum(EU_RecQty) / Sum(Acres))), Location, Grower)),
sum(aggr(If(Sum(WalkbyQty) > 0, (Sum(EU_RecQty) / Sum(Acres)) + (Sum(WalkbyQty) / Sum(Acres)),(Sum(EU_RecQty) / Sum(Acres))), Location, Grower, RanchName)),
sum(aggr(If(Sum(WalkbyQty) > 0, (Sum(EU_RecQty) / Sum(Acres)) + (Sum(WalkbyQty) / Sum(Acres)),(Sum(EU_RecQty) / Sum(Acres))), Location, Grower, RanchName, Commodity)),
sum(aggr(If(Sum(WalkbyQty) > 0, (Sum(EU_RecQty) / Sum(Acres)) + (Sum(WalkbyQty) / Sum(Acres)),(Sum(EU_RecQty) / Sum(Acres))), Location, Grower, RanchName, Commodity, WorkOrder)))</LI-CODE><P> </P>Mon, 23 Aug 2021 15:21:37 GMThttps://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830291#M68426Kushal_Chawda2021-08-23T15:21:37ZRe: Sum formula is not working correctly in pivot table
https://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830571#M68441
<P>Hello Kushal,</P><P>I'm still seeing strange behavior after using your formula (adjusted to add a comma after "dimensionality()"). I'm only getting the right numbers after drilling down to level 3 in the Rows section.</P><P>Any suggestions?</P><P>Thank you for your time.</P>Mon, 23 Aug 2021 15:09:10 GMThttps://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830571#M68441mikegrattan2021-08-23T15:09:10ZRe: Sum formula is not working correctly in pivot table
https://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830576#M68443
<P><LI-USER uid="49220"></LI-USER> make sure that you are using actual field name in Aggr function. For eg. I have written <STRONG>Location</STRONG>, but in your case field name might be LOCATION_NAME. It will be hard to tell what's going wrong without looking into your data.</P>Mon, 23 Aug 2021 15:23:41 GMThttps://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830576#M68443Kushal_Chawda2021-08-23T15:23:41ZRe: Sum formula is not working correctly in pivot table
https://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830580#M68445
<P>There was one field name that had to be changed; Commodity should be CommDesc (Commodity is a valid field name, but CommDesc is being used in the pivot table). I have changed the formula to use CommDesc, but there is still an aggregation issue at level one (Location) and level two (Grower). After I drill down so level 3 (Ranch) is showing, the numbers are good. </P>Mon, 23 Aug 2021 15:33:06 GMThttps://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830580#M68445mikegrattan2021-08-23T15:33:06ZRe: Sum formula is not working correctly in pivot table
https://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830581#M68446
<P><LI-USER uid="49220"></LI-USER> would you be able to share sample app? with expected output at level 1 and 2?</P>Mon, 23 Aug 2021 15:34:46 GMThttps://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830581#M68446Kushal_Chawda2021-08-23T15:34:46ZRe: Sum formula is not working correctly in pivot table
https://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830583#M68447
<P>I will make a sample version of the app and post it later today. Thank you.</P><P> </P>Mon, 23 Aug 2021 15:37:53 GMThttps://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830583#M68447mikegrattan2021-08-23T15:37:53ZRe: Sum formula is not working correctly in pivot table
https://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830597#M68448
<P>Here's a sample copy of the application. I've reduced the amount of data and removed all unnecessary sheets and charts; only the pivot table is included, and I've modified it so only the fields involved in the formula are in the table. With those modifications, it appears the formula you suggested is showing correct numbers after you drill down to level two. I'm not sure why it behaves this way, so any insight you can provide will be very appreciated. Thank you.</P><P> </P><P> </P>Tue, 14 Sep 2021 14:24:48 GMThttps://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830597#M68448mikegrattan2021-09-14T14:24:48ZRe: Sum formula is not working correctly in pivot table
https://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830638#M68452
<P><LI-USER uid="49220"></LI-USER> in your sample what should be the value at level 1? Could you give one example?</P>Mon, 23 Aug 2021 21:12:49 GMThttps://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830638#M68452Kushal_Chawda2021-08-23T21:12:49ZRe: Sum formula is not working correctly in pivot table
https://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830642#M68453
<P>If you filter on Year = 2021, CropType = LTC, and Location = SAL, the Adj Yield value at level one (SAL) shows up as 689. If you drill down to level 3 to show Ranches under a Grower, the value at level one changes to 1064 which is the correct number. Note that the Adj Yield column is my original formula and the Adjusted Yield column is your suggested formula.</P>Mon, 23 Aug 2021 21:23:43 GMThttps://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1830642#M68453mikegrattan2021-08-23T21:23:43ZRe: Sum formula is not working correctly in pivot table
https://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1832672#M68725
<P><LI-USER uid="336"></LI-USER>,</P><P>I haven't been able to modify your formula to work correctly at all levels. Is there another approach I should consider?</P>Tue, 31 Aug 2021 20:57:40 GMThttps://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1832672#M68725mikegrattan2021-08-31T20:57:40ZRe: Sum formula is not working correctly in pivot table
https://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1832697#M68728
<P><LI-USER uid="49220"></LI-USER> Your data model looks complex and this is the reason why you are facing this issue. Issue is that you have null values for Commodity dimensions, so when you drill down to to lower level pivot does not show null values and calculates correctly. At higher level, sum function always sums entire set of values. To avoid this one workaround is below.</P><P>If(Sum({<Commodity={"*"}>}WalkbyQty) > 0, (Sum({<Commodity={"*"}>}EU_RecQty) / Sum({<Commodity={"*"}>}Acres)) +<BR />(Sum({<Commodity={"*"}>}WalkbyQty) / Sum({<Commodity={"*"}>}Acres)),<BR />(Sum({<Commodity={"*"}>}EU_RecQty) / Sum({<Commodity={"*"}>}Acres)))</P><P>I am not sure if this completely solve the issue, but if at other level null values comes up and you don't want to consider that then you may also need to include the condition for that dimension like eg.</P><P>Sum({<Commodity={'*'}, Rower={'*'}>}Acres)</P>Tue, 31 Aug 2021 23:54:56 GMThttps://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1832697#M68728Kushal_Chawda2021-08-31T23:54:56ZRe: Sum formula is not working correctly in pivot table
https://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1832937#M68753
<P><LI-USER uid="336"></LI-USER> Yes, that helps to explain what's going on. Some of the data being loaded doesn't include Commodity, so that could leave null values in certain analyses. I will try your suggestions. Thank you.</P><P> </P>Wed, 01 Sep 2021 14:21:16 GMThttps://community.qlik.com/t5/App-Development/Sum-formula-is-not-working-correctly-in-pivot-table/m-p/1832937#M68753mikegrattan2021-09-01T14:21:16Z