cancel
Showing results for 
Search instead for 
Did you mean: 

PastDay formula

Laris_Fdz
8 - Cloud Apps
8 - Cloud Apps

Hi, Sisense Community,

I use DiffPastPeriod() and PastDay() to show previous day's values or to calculate the difference with the previous day. BU the problem is that I don't have data for Sundays and it seems like the formulas DiffPastPeriod() and PastDay()  actually refer to the -1 day, not the previous day in the dataset so I cannot compare values on Monday (I would expect Saturday data to be compared to Friday's) and Tuesday (I would expect to campare Mondays' data with Saturday's).

Is there a way to solve the problem? I was thinking abouat ranking the two recent days like 1 and 2 and filtering on that ranking. Is there a better way?

1 ACCEPTED SOLUTION

harikm007
12 - Data Integration
12 - Data Integration

@Laris_Fdz ,

If you are looking for values as in highlighted column, please try below steps:

harikm007_0-1648738080643.png

STEPS:

1. Create  a custom column 'weeknumber' in table with below formula:

DayofWeek([Date])

2. Use below formula in widget

case when(([Max weeknumber],Prev([Days in Date], 1)) = [Max weeknumber]-1)
	Then ([Total value],Prev([Days in Date], 1))
	else ([Total value],Prev([Days in Date], 2))
end

-Hari

 

View solution in original post

4 REPLIES 4

ronenavidor
Sisense Team Member
Sisense Team Member

Hi there,

your best option is to solve within the data model. If you data doesn't produce any value for Sunday, the just rollover the value you have on Sat to Sun using custom SQL. This more of a data issue that will need to be fixed within the data model.

Hope this makes sense.

Ronen

 

Laris_Fdz
8 - Cloud Apps
8 - Cloud Apps

Hi, Ronen,

thank you for the advise. Indeed it could be an option for some reasonable-sized model, my is too big)

 

Larisa

harikm007
12 - Data Integration
12 - Data Integration

@Laris_Fdz ,

If you are looking for values as in highlighted column, please try below steps:

harikm007_0-1648738080643.png

STEPS:

1. Create  a custom column 'weeknumber' in table with below formula:

DayofWeek([Date])

2. Use below formula in widget

case when(([Max weeknumber],Prev([Days in Date], 1)) = [Max weeknumber]-1)
	Then ([Total value],Prev([Days in Date], 1))
	else ([Total value],Prev([Days in Date], 2))
end

-Hari

 

Hi, Hari,

so, PREV() accepts an offset parameter! 

Thank you, it is a very nice solution.

Larisa