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
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.
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.
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
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.
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.
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)
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.
Quote from: dougp on 16 Jan 2019 12:20:00 PM
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
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.