Best Of

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.

Hi @Kumar619

You can add annotations to some graphs in analyzer if you need to note a specific instance.

We also have use cases where this would be a really great feature. I understand that you could do it via an app or hack it using a disconnected webform, but the ability to do it in-line would be really powerful.

@swagner Some sample data might help with troubleshooting what is going on here, but I also wonder if considering a different approach might be easier. Have you considered using Magic ETL to get the totals you need? You could use a Group By tile to group by salesperson and year, then use the Rank & Window Lag function to put the previous year's sales total next to the current year's sales totals. You can then use the calculator or formula tile to easily calculate the bonus.

Would that work for you?