Show last month with data

[intapiuser]

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:

1. 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: