Skip to content

    • Charting

    Charting

    SubTotal Category Remaining Amount: How to calculate difference between two columns

    Member
    in Charting

    How to display the value of "Remaining Amount' in subtotal category:

    I have the following scenario, using a Pivot table Displaying the budget amount and actual amount along with remaining amount.

    for every subcategory I do not want to display remaining amount if either value budget or Actual value is 0.

    However I will still like to display a value for remaining amount if either the Budget or Actual value exist (My understanding is that I the value rolls up and adds to display subtotal values).

    Below is the formula's I used to calculate Remaining Amount in BeastMode calculation.

    CASE

    when 

    sum(case when `Transaction_Type` = 1 then `Amount` else 0 end) =0

    or

    sum(case when `Transaction_Type` = 0 then `Amount` else 0 end) =0

    then 0

    else

    sum(case when `Transaction_Type` = 1 then `Amount` else 0 end)

    -

    sum(case when `Transaction_Type` = 0 then `Amount` else 0 end)

    end

    image.png

    Thank you

    ~Monika

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Answers

    • Contributor

      Hi,

      Instead of 0 in your code can you put '' in the beast mode. So if budget or actual value is 0 then '' .

      Does that give you what you need?


      Thanks

    • Coach

      Hi @msharma

      Because the way pivot tables behave and the fact that subtotals are an all or nothing setting what you're looking to do isn't possible at this time.

      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**
    • Coach

      @msharma ,

      @rahul93 is partially correct, if you put a '' in your ELSE clause you will convert your beastmode into a string. It therefore wouldn't format or behave like a number after that point. probably not desireable.

      To get 'nothing' as Rahul is recommending, you can however use ELSE NULL


      1. sum(case when `Transaction_Type` = 1 then `Amount` else null end) 
      2. -- or
      3. sum(case when `Transaction_Type` = 1 then `Amount` end) =0


      Now that said you have to be careful with null

      if you take 

      1. 5 - NULL
      2.  

      the result witll be NULL

      @GrantSmith talks about this at the IDEAS Exchange Conference, https://www.youtube.com/watch?v=gO8OLpsAk4M&list=PLUy_qbtzH0S6-5oDbx3BsIv2Xk-JxJxWi&index=6


      SOLUTION

      test if your amounts net to 0 if so replace with null

      1. case 
      2. when sum(case when ... then amount else 0 end) - sum(case when ... then budget else 0 end) = 0 then null 
      3. else sum(case when ... then amount else 0 end) - sum(case when ... then budget else 0 end)
      4. end


      THAT SAID. WHAT YOU'RE ASKING FOR IS LOGICALLY INAPPROPRIATE

      Null and 0 are not the same thing.

      If i had 5 dollars and spent 5 dollars i have zero dollars left.

      That's different from I spent nothing and i had no budget therefore the value should be null.

      Jae Wilson
      Check out my 🎥 Domo Training YouTube Channel 👨‍💻

      **Say "Thanks" by clicking the ❤️ in the post that helped you.
      **Please mark the post that solves your problem by clicking on "Accept as Solution"
    • Contributor

      @jaeW_at_Onyx


      Hi, using a '' doesn't convert the field into a string field. I just tested it and it works as a numeric field. See screenshots

      image.png
      image.png



      Thanks

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories