Beast Mode

carthur

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

GrantSmith

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.

SUM(CASE WHEN
…
…
THEN
`INVOICE_AMT`
END
)

MarkSnodgrass

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.

GrantSmith

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.

SUM(CASE WHEN
…
…
THEN
`INVOICE_AMT`
END
)

MarkSnodgrass

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.