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.
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
2. 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:
3. 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)
- Use it as a widget filler in order to show last month of data compared with previous month with data: