Pivot Table summary values

alexk
alexk ⚪️
in Charting

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

Screen Shot 2022-07-19 at 11.15.43 AM.png
Screen Shot 2022-07-19 at 11.18.27 AM.png


Tagged:

Answers

  • Ashleigh
    Ashleigh Florida 🟣

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

  • RobSomers
    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.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • alexk
    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
    RobSomers 🟢

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

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • alexk
    alexk ⚪️
    edited July 2022

    @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
    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.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • alexk
    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
    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.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

Categories