Beast Mode formula: not calculating/populating

DomoNewbie
DomoNewbie Member
edited January 2022 in Magic ETL

Hi - i created several Beast Mode formulas (TQ, LQ, QoQ). I have no issues adding TQ and LQ to a Pivot Table chart. When I add QoQ beast mode to the Pivot Table, no values are populated.

image.png

The QoQ formula is a difference of TQ and LQ. I can add TQ to TQ, or LQ to LQ and get a result. I just don't get a result if I mix TQ and LQ.

Is it a Domo performance issue? Eventually I want to divide the difference with LQ. What do you think the issue is?

Thanks in advance!


Beast Mode formula:

--2_Quarters_ago

((CASE WHEN (QUARTER(CURDATE())=1) THEN (CASE WHEN ((year(`Quarter`)=(year(CURDATE()) - 1)) AND (QUARTER(`Quarter`)=3)) THEN `Value` END )

ELSE (CASE WHEN ((year(`Quarter`)=year(CURDATE())) AND (QUARTER(`Quarter`)=(QUARTER(CURDATE()) -1))) THEN `Value` END ) END) -

--LastQuarter

(CASE WHEN (QUARTER(CURDATE())=1) THEN (CASE WHEN ((year(`Quarter`)=(year(CURDATE()) - 1)) AND (QUARTER(`Quarter`)=4)) THEN `Value` END )

ELSE (CASE WHEN ((year(`Quarter`)=year(CURDATE())) AND (QUARTER(`Quarter`)=(QUARTER(CURDATE()) -1))) THEN `Value` END ) END) )

Tagged:

Comments

  • MarkSnodgrass
    MarkSnodgrass Coach

    I noticed some of your case statements don't have an ELSE clause. I would start by adding ELSE 0 to those as Domo doesn't like subtracting when one value is blank.

    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.
  • DomoNewbie
    DomoNewbie Member
    edited January 2022

    Hi Mark - I added else 0 in the following places found in the below code. Seems like it's subtracting the TQ and LQ. But now the LQ formula is not displaying any values (even though that beast mode is independent of the QoQ one.


    --2_Quarters_ago

    ((CASE WHEN (QUARTER(CURDATE())=1) THEN (CASE WHEN ((year(`Quarter`)=(year(CURDATE()) - 1)) AND (QUARTER(`Quarter`)=3)) THEN else 0 `Value` END )

    ELSE (CASE WHEN ((year(`Quarter`)=year(CURDATE())) AND (QUARTER(`Quarter`)=(QUARTER(CURDATE()) -1))) THEN `Value` else 0 END ) END) -

    --LastQuarter

    (CASE WHEN (QUARTER(CURDATE())=1) THEN (CASE WHEN ((year(`Quarter`)=(year(CURDATE()) - 1)) AND (QUARTER(`Quarter`)=4)) THEN `Value` else 0 END )

    ELSE (CASE WHEN ((year(`Quarter`)=year(CURDATE())) AND (QUARTER(`Quarter`)=(QUARTER(CURDATE()) -1))) THEN `Value` else 0 END ) END) )


    image.png


  • Ashleigh
    Ashleigh Coach

    @DomoNewbie perhaps its not finding the right timeframe. I would make a new beastmode to double check that Quarter(curdate) and all the other dates are actually returning what you are looking for. I would also double check if you have fiscal calendar enabled or not.

    **If this answer solved your problem be sure to like it and accept it as a solution!

Categories