New post
Question
· Mar 4, 2017

Calculated Fields

#InterSystems IRIS BI (DeepSee)

  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.

Thanks!

Discussion (3)0
Log in or sign up to continue
Brendan Bannon · Mar 6, 2017

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:

s YearMonth=$P($ZDATE($H,3),"-",1,2)
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.

0 0
Peter Steiwer · Mar 7, 2017

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

1 0