How to override the filter set on cards and calculate sum on a specified period and condition

How to add hardcoded dates that the amount is summed for a period specified, even when the chard is filtered on for different dates.

e.g. I will like to always get the sum of transactions Type 1 between the period of Jan-01-2020 and Dec-31-2021, even when the filter on the card is set for Jan-01-2021 and Feb-28-2021


As seen in the image, the Budget_Total also gets filtered based on the filter applied to chart.

I have used the following formula in Beast Mode to capture Budget Total:

sum(sum(case when `Transaction Type` = 1 then `Amount` else 0 end)) over (partition by `G_L Account No_`)


Also if possible I will prefer to have a single entry for every 'G_L Account No_', not a repetition.

Thanks you

Answers

  • GrantSmith
    GrantSmith Indiana 🟤

    Hi @msharma

    The filter is applied before any beast mode is calculated so if the card is filtered for Jan&Feb 2021 you won't have access to the 2020 data points. If you need that data point then you'd likely need to calculate it in a dataflow before hand.


    As for your G_L_ACCOUNT No_ field - you have different values for the amount, which value are you wanting to have for the amount? Because there's different values you have different rows. You could aggregate the data using either min / max / avg / sum etc to calculate the amount you're looking for (I'm assuming SUM in your case).

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    @msharma think of analyzer as an engine for writing a SQL query.


    When you add filters, you're adding a WHERE clause.

    Your Beast Modes are part of the SELECT clause.


    in SQL your execution order is FROM - WHERE - GROUP BY - SELECT - ORDER BY.

    Same applies in Analyzer.

  • msharma
    msharma ⚪️

    Thanks for the clarification.

    I tried to create a new table in DOMO SQL using the following syntax in sql :

    SELECT `G_L Account No_`,`AcctLen`,`fund#`, `Fund No_`,`Dept#`, `Obj#`, `Global Dimension 1 Code`, `Posting Date`, `Description`, `Amount`, `Transaction Type`, `Actual_Amt`, `Budget_Amt`,

    sum(sum(case when `Transaction Type` = 1 then `Amount` else 0 end)) over (partition by `G_L Account No_`) as NewSum

     from g_l_entry

    I get this error:


    where as when I use Group BY I get no error.

    SELECT `G_L Account No_`, sum(case when `Transaction Type` = 1 then `Amount` else 0 end) as NewSum

     from g_l_entry


    Is Partition by not supported in DOMO SQL?


    Thanks,

    Monika

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!