Date Filters - Page Level / Card Level

AvinashK
AvinashK Member
in Charting

l have some data like below :

Date (MM/DD/YYYY)Count
1/1/20192
1/2/20192
1/3/20192
1/4/20192
1/5/20192
1/6/20192
1/7/20192
1/8/20192
1/9/20192

and so on till Nov 30th (Daily Data)

Card level : I have used all time data and trying to get the sum of count according to the page filter (filter card). 

 

If the page filters is from Apr 10th - Sept 10th 

I want to get the data from the month of August (last full month), July ( Last full month -1) 

 

Some more cases:  Page Filters: Dec 1st - Jan 13th - Need values of Dec and Nov

 

How to write a beast mode code to get the data according to the page level filter( another filter card )

 

 

(CASE WHEN MAX(MONTH(`Date 1`)) = 1 THEN (CASE WHEN MONTH(`Date 1`) = 12 AND YEAR(`Date 1`) = MAX(YEAR(`Date 1`)) -1 THEN 'Yes' ELSE 'No' END) ELSE (CASE WHEN YEAR(`Date 1`) = MAX(YEAR(`Date 1`)) AND MONTH(`Date 1`) = MAX(MONTH(`Date 1`)) -1 THEN 'DblYes' else 'DblNo' END )END)

 

The logic in the italic case condition is not working. Please help me out.

Best Answer

  • TannerL
    TannerL Domo Employee
    Answer ✓

    Hello @AvinashK ,

     

    A beast mode can only use data within the filtered range for calculations.  This means a filter for Dec 1st through Jan 13th would exclude the November data you're looking for.

    Similarly, you're not able to filter on a beast mode with an aggregate function like the Max() you’re using in your example.    

     

    Thanks!

     

    Tanner

    Domo EE
    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • TannerL
    TannerL Domo Employee
    Answer ✓

    Hello @AvinashK ,

     

    A beast mode can only use data within the filtered range for calculations.  This means a filter for Dec 1st through Jan 13th would exclude the November data you're looking for.

    Similarly, you're not able to filter on a beast mode with an aggregate function like the Max() you’re using in your example.    

     

    Thanks!

     

    Tanner

    Domo EE
    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Effie
    Effie Contributor

    You can use a Window function in the Beast Mode as they apply OVER the rows that are filtered. Just bear in mind that the Window functions are not applicable when you have selected to show table totals or subtotals.

  • AvinashK
    AvinashK Member

    @TLarsen 

     

    Thanks Larsen for the clarification. 

     

    Is there any other way to get the maximum date from a dataset. 

    I want to get data for a month and 2 months prior to the maximum data present in the data. 

    If data is still Nov 2019 , I want to get data for Oct 2019 & Sep 2019

  • AvinashK
    AvinashK Member

    Hi @Effie 

     

    Can you elaborate on window functions?

  • [Deleted User]
    [Deleted User] Member

    Have you had a chance to review the Beast Mode documentation in our Knowledge base. Have a look here:

     

    https://knowledge.domo.com/Special:Search?q=beast+mode

This discussion has been closed.

Categories