how to get prior 1 day var value for current date but exculde national holidays

[khabaleshri]

I have done following so far.
1st query : I have created current query for clean p&l measure and date
2nd query : same query but for prior 1 day var,

detail filters :(( _day_of_week (?Start_Date?,1)=1 or _day_of_week (?End_Date?,1)=1) AND ([Date] between _add_days(?Start_Date?,-3) and _add_days(?End_Date?,-3)))

OR

(( _day_of_week (?Start_Date?,1)<>1 or _day_of_week (?End_Date?,1)<>1) AND ([Date] between _add_days(?Start_Date?,-1) and _add_days(?End_Date?,-1)))
and Business Day Flag='Y'(represent national as well as weekend holiday)

and created data item Current Date : if (_day_of_week([Date],1)=5) then (cast(_add_days([Date],3),date))
else (cast(_add_days([Date],1),date))

current date data item created to join query based on date

3rd query : joined current and prior query based on LOB and date

example

CURRENT
Date                         1 day var      profilt loss
24/12/2018      100                50
26/12/2018      200               100
27/12/2018      300               200

PRIOR
Date                1 day var    profit loss
23/12/2018   50            25
24/12/2018   100            50
26/12/2018   200            100

expected outcome :

Date                1 day var    profit loss
24/12/2018   50            50
26/12/2018   100            100
27/12/2018   200            200
date and profit loss for current date but need prior 1 day VAR

I have achieved above for weekdays and exclude weekend but how to exclude national holidays.
how to use business day flag in current date data item
now current date : if (_day_of_week([Date],1)=5) then (cast(_add_days([Date],3),date))
else (cast(_add_days([Date],1),date))


or is there any other way to achive expected outcome? thanks

[dougp]

I have the luxury of a DATE table that has columns with names like HolidayIndicator, LegalStateHolidayIndicator, and LegalStateHolidayIndicator.  These return 'no' or 'yes'.

In the absence of that, use brute force.  Include a [lengthy, proprietary, error-prone] CASE or IF statement that looks for all of your national holidays.

It looks like you are adding 3 days when you find a weekend.  Something like this embedded in your statement can be used to add 1 [more] day when a holiday is found.

Code Select Expand

case
  when
    case
      when [Date] = 2019-01-01 then 'yes'
      when ...another holiday... then 'yes'
      when ...another holiday... then 'yes'
      when ...another holiday... then 'yes'
      when ...another holiday... then 'yes'
      when [Date] = 2019-07-04 then 'yes'
      when ...another holiday... then 'yes'
      when ...another holiday... then 'yes'
      when ...another holiday... then 'yes'
      when [Date] = 2019-12-25 then 'yes'
      else 'no'
    end = 'yes'
  then 1
  else 0
end
Of course, that may cause problems if the holiday is on a weekend.  But I'm sure you can work that out.

[khabaleshri]

Hi Dougp,

Thanks for your prompt suggestion but I too have business day flag which shows Y for working days and N for holiday and weekend.
My requirement is if one run a report for 26th Dec,2018 (wed) then one should get fact(1Day VAR value) for 24th Dec,2018(mon)
Because we have holiday on 25th and so on.
Could you please tell me stepwise how to achieve this. How many queries are needed and how to join them etc.

Final display :
Date                | LOB  | Clean P&L               | prior 1 day VAR
26/12/2018   | A      | P&L of 26/12/2018| 1 day VAR of 24/12/2018

[dougp]

Use running_count() to get something like this:


CURRENT
RowNum     Date      1 day var    profit loss
  1     24/12/2018      100             50
  2     26/12/2018      200            100
  3     27/12/2018      300            200

PRIOR
RowNum     Date      1 day var    profit loss
  1     23/12/2018       50             25
  2     24/12/2018      100             50
  3     26/12/2018      200            100


Then join on RowNum.

[khabaleshri]

thanks dougp. I have used the same logic but later I am facing one problem.
when user select start date from date prompt and if that start date is Monday then it will not give me prior day because prior day is sunday.
currently I am using below filter to get prior data.
date between _add_days(?start_date?-1) and _add_days(?end_date?-1)

similarly if user select start date 26th dec,2018 then above filter wont give me 24th dec,2018.
so how to exclude weekend and national holiday while getting prior date data. I have weekend and holiday flag (Y or N) but I have only weekdays records that means flag always Y.
prior filter is doing arithmetic operation like 26-1 =25 but we don't have data for 25th dec(holiday).
so how to get prior available date in this case? thanks.

[Andrei I]

You should try to use Cognos functions, available out of the box.
It depends on which DB datasource you have but generally works.

Essentially  "prior 1 day var" can be as simple  as this:   running-difference ([Measure])

See the attached Report spec for exact details (you have to modify it according your environment)

[dougp]

I took Stork's example and completed it (used a package, etc.) -- attached.  I excluded only weekends.  I'll let you do the hard work of excluding holidays.  I still don't think it does what you want...

If I run it for 2013-01-07 to 2013-01-11 (January 7 being a Monday) I get data from January 8-11.  I assume you are looking for the comparison between January 4 and January 7 as well.

[Andrei I]

I took Stork's example and completed it (used a package, etc.) -- attached.  I excluded only weekends.  I'll let you do the hard work of excluding holidays.  I still don't think it does what you want...

If I run it for 2013-01-07 to 2013-01-11 (January 7 being a Monday) I get data from January 8-11.  I assume you are looking for the comparison between January 4 and January 7 as well.

Doug,
If the fact table does not have values for every day then we should create a Query with 0 values for those days.

So updated version has three Queries:
1 - Days - to get list of all required Business Days based on the Business Day Flag and 2 range.

2 - Measures - Measure  by Date

3 - Main - Left outer join on Date between Days and Measures

See attached for details

[khabaleshri]

Stork\dougp,
I will try your efforts tomorrow and let you know on the results. Thanks.

Yes. if you run the report for 2013-01-07 to 2013-01-11(January 7 being a Monday ) , then prior 1 day VAR should be between January 4 -10 but suppose January 4 will be the US holiday then data should be January 3 for current Jan 7 and so on.
But currently filter date between _add_days(?start_date?-1) and _add_days(?end_date?-1) gives me data for January 8-10 because January 6 being weekend and we don't have such records in fact table .

Also my fact table only have working days data so business day flag always be Yes.