Pivot Table summary values

alexk

Hi all, I'm very confused about what is going on with my pivot table.

The row&column totals are correct but the underlying cell values within the table are only showing a value from one part of the sum instead of the entire sum? What's worse, when I drill down, it shows the correct sum

Tried moving to a Mega Table and the readability is just not the same.

My next guess was to create a rollup dataset and nix the drill down capability altogether

Appreciate you all

Ashleigh

@alexk are you sorting on anything? Sometimes that can cause issues.

RobSomers

@alexk, how large is your dataset? Sometimes with pivot tables if the dataset is huge, it won't display all the data in each category row.

alexk

Thanks friends!@Ashleigh I'm sorting on a beast mode but doesn't look like it removing it made much difference :(

@RobSomers It is pretty large... the dataset has 5.5M. When I apply filters to the pivot table card, it brings it down to about 10k. Do filters not help with "reducing the size"? What's weird is that it was working until I added a beast mode to filter out parts of the dataset

For context, the use case is to look at snapshots of a SFDC object on any given day. Ideally, we can view the trend in a bar/line chart and use a pivot table to see the $ breakout and drill down to which deals were forecasted on which date

RobSomers

Could you post your beast mode that you're using as a filter?

alexk

@RobSomers

A colleague recommended I move the transform to MagicETL so it's a formula card now instead of a beast mode.

The transform is a bit confidential so can't really share the exact statement. In essence, it adds a column to pull through existing $ value for each record depending on 3/4 sets of criteria

RobSomers

Could you post it with the columns and values used replaced with other names and values, so we can just see what the formula looks like? What the actual columns/values are shouldn't matter.

alexk

Hopefully this is enough?

CASE WHEN `Stage`='7'

   THEN $

WHEN `Record Type` IN ('A','B') 

   AND `Stage` NOT LIKE '%7%'

   AND `Score`>= #

   AND DATEDIFF(`Snapshot Date`,`Last Modified Date`)<= #

   THEN `$`

   

  WHEN `Record Type` IN ('A','B') 

   AND `Score` IS NULL

   AND `Stage` NOT LIKE '%7%'

   AND ((`Stage`='1' AND `Days in Current Stage`<=#) OR 

      (`Stage`='2' AND `Days in Current Stage`<=#) OR

      (`Stage`='3' AND `Days in Current Stage`<=#) OR

      (`Stage`='4' AND `Days in Current Stage`<=#) OR

      (`Stage`='5' AND `Days in Current Stage`<=#) OR

      (`Stage`='6' AND `Days in Current Stage`<=#))

   THEN `$`

   

  WHEN `Record Type` IN ('C','D')

   AND `Score`>#

   AND `Stage` NOT LIKE '%7%'

   THEN `$`*#

   

  WHEN `Record Type` IN ('C','D')

   AND `Score`<=# AND `Score`>#

   AND `Stage` NOT LIKE '%7%'

   THEN `$`*#

   

  WHEN `Record Type` IN ('C','D')

   AND `Score`<=50 OR `Score` IS NULL

   AND `Stage` NOT LIKE '%7%'

   THEN `$`*#

   

  END

RobSomers

I would put ELSE 'Unknown' right before the end to more easily identify cases where none of your rows meet them conditions and see if there's anything that should have a value but doesn't. Then you can see if that has anything to do with what's missing.