Date Filters - Page Level / Card Level

AvinashK

l have some data like below :

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

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.

TLarsen

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!

 

Effie

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

@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

Hi @Effie 

 

Can you elaborate on window functions?

DaniBoy

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