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

msharma
msharma Member
in Charting

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


image.png

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 Coach

    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).

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @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.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • msharma
    msharma Member

    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:

    image.png


    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

Categories