cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

The last month with data isn't the current calendar month (payroll for instance).The out of the box feature of Sisense supports only the last calendar month 

Below shows a solution  to have a default filter that shows the most updated month with data.

Community_Admin_0-1634206648977.png

Solution:

  • Add a custom table to the ElastiCube that holds the months order, this table will have two fields: MonthKey and order, one row for each month
    Example of Month_Order custom table script:

SELECT MonthKey, rankdesc(MonthKey) MonthsOrder

FROM 

(SELECT DISTINCT MonthKey FROM [fact1] ) m

  1. Create a custom table for time dimension (which can use the CSV file as a source) and add a field that indicates what is the month order for each day

SELECT Date,dc.monthkey,monthsorder

FROM

     (SELECT Date,100*getyear(Date)+getmonth(Date) monthkey 

FROM [Date Dimension.csv]  

WHERE Date > createdate(2008,1,1)) dc --restricts only dates since 2008

JOIN [Months_Order] mo ON mo.MonthKey=dc.MonthKey

ElastiCube screenshot:

 

Community_Admin_1-1634206648974.png
  1. In the dashboard use the time dimension field you added in order to show the relevant months

For example:

  • Use it as a dashboard filler in order to show last month of data: set MonthOrder as 1 (you can set it as background filter)
Community_Admin_2-1634206649104.png
  • Use it as a widget filler in order to show last month of data compared with previous month with data:
Community_Admin_3-1634206648976.png
 
Version history
Last update:
‎10-14-2021 03:18 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request