PastDay formula

[Laris_Fdz]

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?

[ronenavidor]

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]

Hi, Ronen,

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

Larisa

[harikm007 · Accepted solution]

@Laris_Fdz ,

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

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

[Laris_Fdz]

Hi, Hari,

so, PREV() accepts an offset parameter! 

Thank you, it is a very nice solution.

Larisa