Best Of

I assume this is data where you took a payment table and JOINed it to a Employee Timesheet table.

Don't JOIN the data.

Append, UNION, the two sets of data together.

I know it sounds like work to understand why UNION is a better solution than JOIN. But trust me.

As you JOIN transaction tables together, you are going to try to do more crazy math to make your cards work. It's not scalable.

Take your LEFT table, add "Activity Type" = "Payment"

Take your RIGHT table add "Activity Type" = "Timesheet"

And UNION the data on conformed column names. You'll avoid row duplication.

https://www.youtube.com/watch?v=PVbOeLSae9o&t=1s

Hi @user06209

You can utilize window functions in a beast mode (you just need to have them enabled - talk to your CSM)

SUM(SUM(`Amount`)) OVER (PARTITION BY YEAR(`Date`) ORDER BY `Date`)

Hi @user048760

What you're looking for is the CONCAT function. It allows you to add multiple columns together with constants to create a single value.

If you're looking for just the URL (which you won't be able to click on but would only be displayed)

CONCAT('https://xxxxxxxxxx.sugarondemand.com/#Calls/', `ID`)

Alternatively you can also utilize some HTML code to convert your URL to a clickable URL.

CONCAT('<A HREF="https://xxxxxxxxxx.sugarondemand.com/#Calls/', `ID`, '" target="_blank">', 'Clickable Text Here Or Use a Column Instead', '</A>')

The `target="_blank" ` tells the HTML to open your link in a new window. If you want it to open in the same window then just exclude that section.

The HTML link will only work in the table type card and doesn't work in other cards at this time.

Hi @richardjmarshall

You've got two options.

1) You could use a beast mode to determine if a date is from 2 months ago

CASE WHEN LAST_DAY(CURDATE() - INTERVAL '2' MONTH) = LAST_DAY(`Date Field`) THEN 1 ELSE 0 END

and then just filter on that value being 1.

This method you're always stuck looking at 2 months ago according to the current month. The next option is better in that it will allow you to look at 2 months ago based on any date you have selected in your chart.

2) The more robust option would be to create a custom date dimension table where you have customized offsets (With a report date and a comparison date). You'd then use a Fusion / View to join that dataset to your main dataset based on your date field and the comparison date field. You then can filter for your offset type of 2 months ago. This has been outlined several times here on the dojo. See https://dojo.domo.com/t5/Card-Building/show-percent-on-period-over-period-graph/m-p/50540/highlight/true#M7313.

I highly recommend method 2 as it gives you much more flexibility when processing and displaying your data.

Also @jaeW_at_Onyx has a nice video he put out which outlines this process as well:

https://www.youtube.com/watch?v=CDKNOmKClms

this is the reason why i try to avoid using period over period cards and instead always recommend building your own date dimension that handles period comparisons for you.

https://www.youtube.com/watch?v=CDKNOmKClms

Hi @msharma since you’re wanting to do a conditional sum you need to use a case statement inside your sum to only select the values you want

sum(sum(case when `Transaction` = 1 then `Amount` else 0 end)) over (partition by year(`Posting Date`))

Repeat for transaction 0

The only spot to do that would be the description field which is just below the card title. You can notes in that field.