Derrek Kegler · Mar 4, 2017

Calculated Fields

  1. 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.

  2. 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.


0 397
Discussion (3)0
Log in or sign up to continue


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:


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

year = birthd.[year].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.

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