Show last month with data
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:
Updated 03-02-2023
