Skip to content

    • Beast Mode

    Beast Mode

    Beast Mode

    Member
    in Beast Mode

    Hello All,

    I want to build a card comparing cy with py grouped by location. My plan is to use bar chart side by side comparing cy to py. My beast mode calculations seem correct when I create a table with them.

    When not comparing to PY, I just built a filter to include the dates i need, and it works fine but when comparing side by side the filter doesn't resolve the issue.

    How do I group or sum the amounts by location, I tried adding the (OVER(PARTITION BY location) in my beast mode but gets an error. Any ideas please?

    Per the screenshot below, i would like the x-axis to be distinct locations please.

    Thank you all

    image.png
    image.png

    Best Answers

    • Coach
      Answer ✓

      You need to wrap your entire case statement in SUM instead of having your condition be the sum.

      It's evaluating each row at a time and then aggregating when you want to aggregate after evaluating all the rows.

      1. SUM(CASE WHEN
      2. …
      3. …
      4. THEN
      5. `INVOICE_AMT`
      6. END
      7. )
      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**
    • Coach
      Answer ✓

      In addition to @GrantSmith 's suggestion of moving the SUM to the outside, you can simplify your current year evaluation by doing something like this:

      YEAR(invoicedate) = YEAR(CURRENT_DATE()) AND invoicedate < = CURRENT_DATE()

      For the previous year you can again just use the YEAR function like this:

      YEAR(invoicedate) = YEAR(CURRENT_DATE())-1

      Hope this helps.

      **Check out my Domo Tips & Tricks Videos

      **Make sure to <3 any users posts that helped you.
      **Please mark as accepted the ones who solved your issue.

    Answers

    • Coach
      Answer ✓

      You need to wrap your entire case statement in SUM instead of having your condition be the sum.

      It's evaluating each row at a time and then aggregating when you want to aggregate after evaluating all the rows.

      1. SUM(CASE WHEN
      2. …
      3. …
      4. THEN
      5. `INVOICE_AMT`
      6. END
      7. )
      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**
    • Coach
      Answer ✓

      In addition to @GrantSmith 's suggestion of moving the SUM to the outside, you can simplify your current year evaluation by doing something like this:

      YEAR(invoicedate) = YEAR(CURRENT_DATE()) AND invoicedate < = CURRENT_DATE()

      For the previous year you can again just use the YEAR function like this:

      YEAR(invoicedate) = YEAR(CURRENT_DATE())-1

      Hope this helps.

      **Check out my Domo Tips & Tricks Videos

      **Make sure to <3 any users posts that helped you.
      **Please mark as accepted the ones who solved your issue.

    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