Prism Business Intelligence Tools - Tips and How-to's

March 2010 - Posts

• Sum of Max

  Yesterday, a customer asked me how to get the total from a max aggregation. This is an interesting question, and I wanted to share the answer with you. Let's say we have three dimensions (Customers, Projects, Items) and we want to get the max number of items in each project:    Let's create a max aggregation on #Items (more info) and call it Max Items. Here you can see an example on a Pivot:     Now, let's create a new custom measure to sum all the max items in Projects: sum ( [Projects] , [Max Items] ) and call it Sum if Max Items, Add this measure to a Numeric Indicator:   Tip: Adding a selector with the Customers dimension will yield the Sum of Max for each selected company. Posted Mar 02 2010, 06:35 PM by Adi with no comments Filed under: Sum of Max, Max function

• Period to Date Calculations

  We frequently want to compare the current month-to-date (MTD) Sales to the sales of the same period in previous month(s), rather than to the sales of entire previous months. For example, if today is the nth day of the month, we would want to compare sales of the first n days in the current month (or quarter or year) to the first n days in the previous month (or quarter or year). PrismCubed has some built-in period-to-date functions that make this comparison very easy to implement. The basic function MTDSum([Sales]) returns the MonthToDate Sales (where Sales is the measure for Sales). The function PastMonth(MTDSum(Sales),M) will return the MTD Sales going back M months (where MTDSum(Sales) is a custom measure presenting the generic MonthToDate sales). As an example, I have created the following custom measures and dropped them on a Pivot: MTDSales = MTDSum([Sales])                      ;Month-to-date sales, current month MTDSales P1M = PastMonth([MTDSales],1)  ;Month-to-date sales of the previous month MTDSales P1M = PastMonth([MTDSales],2)  ;Month-to-date sales of two months ago MTDSales P1M = PastMonth([MTDSales],3)  ;Month-to-date sales of three months ago     The figures in Pivot 1 above show the aggregated sales of the 17 first days in each of the following months: November, October, September and August 2009. Changing the Date will automatically adjust the figures respectively, i.e. in Pivot 2 above the figures show aggregated sales of the first 23 days in each of the following months: June, May, April and March 2009. Similarly, PrismCubed provides these quarter-to-date and year-to-date functions: QTDSum(X), YTDSum(X), PastQurater(X) and PastYear(X) These functions can be used in Pivots, Charts and Indicators. The following example shows various aggregations as of the 17th of November, 2009: MTDSales P1M - Sales of the first 17 days in October 2009 MTDSales P12M - Sales of the first 17 days in November 2008 QTDSales - Aggregated Sales in the period 1.10.2009 through 17.11.2009 QTDSales P1Q - Aggregated Sales in the period 1.7.2009 through 17.8.2009 QTDSales P4Q - Aggregated Sales in the period 1.10.2008 through 17.11.2008 YTDSales - Aggregated Sales in the period 1.1.2009 through 17.11.2009 YTDSales P1Y - Aggregated Sales in the period 1.1.2008 through 17.11.2008 Posted Mar 01 2010, 08:36 PM by Adi with 2 comment(s)