Best Of

Re: November Community Recap 🦃🍂

Re: Are there any other data points you'd like to see me include in these monthly posts?

What about more information on ideas from the Ideas Exchange? How many have been implemented this month? How many are under review? Which users have had ideas they suggested incorporated into the product?

I'd also like to incentivize posts being written in a more entertaining fashion. I don't think I've ever seen anyone use the LOL reaction, so I'd like both metrics on who induced the most LOLs and links to those posts so I can see them (and verify for myself that they are indeed LOL-worthy).

DavidChurchman

3

Re: I need help with my equation

In addition to the correction mentioned by @ggenovese, this segment also needs to change: date(Job Open Date)<'10/01/2024' . An unfortunate quirk of Magic ETL is that comparison operations with strings always happen as strings, which is usually not what you want. You must convert the right side of the comparison to a date as well: date(Job Open Date)<date('10/01/2024')

rco

1

Re: I need help with my equation

I'm having a little trouble following the logic, it might be helpful if you shared your excel formula. Is the case statement you shared supposed to produce the New Fee A value, and you'll have another for New Fee B?

Also, should this part of the expression:

 (Charge(Proceeds A/(Proceeds A+Proceeds B))+Proceeds A)

be changed to:

 (Charge * (Proceeds A/(Proceeds A + Proceeds B))+Proceeds A)

ggenovese

1

Re: Export Magic ETL Code for External Version Control

@MasonW you can also use the CLI to export the JSON definition of the dataflow using the 'list-dataflow' command. This could be automated through scripting if needed.

ellibot

3

Re: Hello from a new user!

Hi

OllyB

1

Re: Dynamic CAGR formula?

Hello @ccccc4,

It's a bit complicated and I'll try to explain in detail how I approached this. First, I've created a Variable for the Start Period as follows:

I applied the same process for the CAGR end period, defining the start and end years for the calculation. Using these variables, I calculated the CAGR with the following formula:

Power(
(SUM(CASE WHEN Parts Kits Fiscal Year=SEG PKA CAGR End Period THEN Parts Kits Qty END)
/
SUM(CASE WHEN Parts Kits Fiscal Year=SEG PKA CAGR Start Period THEN Parts Kits Qty END))
,1/(SEG PKA CAGR End Period-SEG PKA CAGR Start Period+1))-1

On the dashboard, you can set the CAGR calculation period (I know, I also hate the decimal in the year).

This automatically calculates the CAGR for the 2019–2023 period in the following graph:

Manasi_Panov

1

Re: Drill Down to an Existing Card

I'm on day 2 of using Domo and already came across this as a limitation. Has there been any progress on this since March?

LucianLeveles

1

Re: Running time intervals based off of an identifier

You can use a window function to do a lag

LAG(`Action Time`) OVER (PARTITION BY `Ticket Number` ORDER BY `Action Time`)

This can also be calculated using a Rank and Window tile in a Magic ETL

Then taking that you can calculate the time difference:

SEC_TO_TIME(UNIX_TIMESTAMP(`Action Time`) - UNIX_TIMESTAMP(`Prior Action Time`))

UNIX_TIMESTAMP will convert the timestamp to the number of seconds since 1970-01-01. Subtracting the two gives the number of seconds. SEC_TO_TIME converts the number of seconds to a human-readable format of hours, minutes, and seconds.

GrantSmith

1

Re: Create Month End Date column with Year & Month columns?

There's a LAST_DAY() Function that gives the last day of the month for a given date:

LAST_DAY(STR_TO_DATE(CONCAT(`Year`,`Month`),'%Y%m'))

ggenovese

3

Re: YTD BeastMode with Variables

I don't have an example set up to try this.

SUM(CASE

    WHEN (

        (Year Selector = Fiscal Year AND Month Number >= 2) -- From February to December of the current fiscal year

        OR

        (Year Selector = Fiscal Year + 1 AND Month Number = 1) -- Include January of the next calendar year

    )

    AND Month Number <= 

        CASE

            WHEN Month Selector = 'January' THEN 1 -- Stop at January for Year Selector = Fiscal Year + 1

            WHEN Month Selector = 'February' THEN 2

            WHEN Month Selector = 'March' THEN 3

            WHEN Month Selector = 'April' THEN 4

            WHEN Month Selector = 'May' THEN 5

            WHEN Month Selector = 'June' THEN 6

            WHEN Month Selector = 'July' THEN 7

            WHEN Month Selector = 'August' THEN 8

            WHEN Month Selector = 'September' THEN 9

            WHEN Month Selector = 'October' THEN 10

            WHEN Month Selector = 'November' THEN 11

            WHEN Month Selector = 'December' THEN 12

        END

    THEN IFNULL(Amount * -1, 0)

    ELSE 0

END)

ArborRose

2