Date Filters - Page Level / Card Level

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

  • TLarsen
    TLarsen ⚪️
    Accepted 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!

     

Answers

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

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

  • Hi @Effie 

     

    Can you elaborate on window functions?

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