Forum Discussion

Laris_Fdz's avatar
Laris_Fdz
Cloud Apps
03-24-2022
Solved

PastDay formula

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?

dashboards & reporting
  • harikm007's avatar
    harikm007
    03-31-2022

    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

     

4 Replies

Replies have been turned off for this discussion
  • harikm007's avatar
    harikm007
    Data Warehouse
    03-31-2022

    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's avatar
      Laris_Fdz
      Cloud Apps
      03-31-2022

      Hi, Hari,

      so, PREV() accepts an offset parameter! 

      Thank you, it is a very nice solution.

      Larisa

  • ronenavidor's avatar
    ronenavidor
    Sisense Employee
    03-28-2022

    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's avatar
    Laris_Fdz
    Cloud Apps
    03-30-2022

    Hi, Ronen,

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

     

    Larisa