Calculated Fields
-
How can I create a calculated field to give the previous date. For example, I need to create a pivot table that shows total payments posted for the prior day.
-
How to create a calculated field to show average daily collections divided by the number of the days passed in a month. So, for today, it would be the average daily collections divided by 4days.
Thanks!
Derrek
From a COS coding point of view you can get yesterday by setting Yesterday = $H-1
To get the number of days this month you can do the following:
w !,YearMonth
s StartDate=YearMonth_"-01"
w !,StartDate
s DaysThisMonth=$H-$ZDATEH(StartDate,3)
w !,DaysThisMonth
of course all that can be squished into one line:
w $H-$ZDATEH(($P($ZDATE($H,3),"-",1,2)_"-01"),3)
Looking at the DeepSee docs it looks like you can get some of the stuff using Now
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...
year = birthd.[year].NOW
month=birthd.[month].NOW
I am not an MDX guy so if this is the direction you want to go in you should talk to one of our DeepSee support people and I am sure they can tell you how to do this.
I believe NOW-1 would give you what you want.
Samuel is correct, using NOW-1 on a Day level will give you the information from yesterday.
For the second part, please see the following two queries:
>>select PERIODSTODATE([DateOfSale].[Actual].[MonthSold],[DateOfSale].[Actual].[DaySold].[NOW]) on 1 from HOLEFOODS
1 Mar 1 2017 *
2 Mar 2 2017 *
3 Mar 3 2017 *
4 Mar 4 2017 *
5 Mar 5 2017 *
6 Mar 6 2017 *
7 Mar 7 2017 *
---------------------------------------------------------------------------
Elapsed time: .009242s
>>select COUNT(PERIODSTODATE([DateOfSale].[Actual].[MonthSold],[DateOfSale].[Actual].[DaySold].[NOW])) on 1 from HOLEFOODS
COUNT 7
---------------------------------------------------------------------------
Elapsed time: .006043s