Hi There,

I recently see many of you request for a calculation between some date and today.

Using the formula Editor you can use the DDIFF function with two parameters to solve this:

1. Now( [Day Level] ) - Returning Today

2. [Day Level] - The day dimension you want to calculate with

Example:

DDiff( Now([CreateDate (Days)]) , [CreateDate (Days)] )

This function returns the amount of days from CreateDate till today.

Have an amazing day!

Hello,

Designing attractive dashboards can sometimes take even longer than preparing the analysis itself.

With SiSense Prism, however, it’s fast and easy!

Check out this PSM dashboard template inspired by the design of the new Google+.

Design Concepts:

• The dashboard enables sales reps to have complete control over the lead pipeline
• Reps can click a lead to get information about them from social networks
• Reps can see if leads from the same domain/IP already exist in the system
• Reps can filter leads according to time zone

 First, what are DAU and MAU?

• DAU Refers to the number of unique users of your application per day by some event (i.e. # unique users that clicked on video) 
• MAU Refers to the number of unique users per the past 30 days by some event.

The main problem is to calculate the MAU due to the fact you need to count the number of unique users for the past 30 days for each date.

1. Assume we have a table called [30days] with 3 fields (Date, User, Events)

2. First we need to create a new custom SQL Expression to have last 30 days for each selected date, lets call it eDate (SelectedDate)

SELECT
    b.[Date] [SelectionDate], a.[Date] [30Date]
FROM
    (SELECT
        [Date]
    FROM
        [30days]
    GROUP BY [Date]) a
INNER JOIN
(SELECT
        [Date]
    FROM
        [30days]
    GROUP BY [Date]) b
ON a.[Date] > AddDays(b.[Date], -30) AND a.[Date] <= b.[Date]
ORDER BY [SelectionDate], [30Date] ASC

3.  To Calculate the DAU we will need to create a new SQL Expression, lets call it eEventsDay. (You can add here a WHERE statement if you would like to limit it to one event)
    

SELECT
    [Date], [User], Distinct_Count([Events]) [EventCount]
FROM
    [30days] a
GROUP BY [Date], [User]

4. Now duplicate this table creating the MAU and call it eEvents

5. Connect the tables to each other using the illustration above.

6. Next we need to build our eCube and start building our dashboard.

Building the Pivot with the MAU DAU analysis

7. Create 2 new filters by Value on the EventCount fields in both eEvents and eEventsDay tables, in this sample we want to see all users with more then one events a day

8. Create two count measures over the User fields in both eEvents and eEventsDay tables and call them [# eEvents Users] and [# eEventsDay Users]

9. Create two Measured Values using the formula editor counting the number of users and the filters we created in 7.  Example: ( [# eEvents Users] , [>=1])

10. Create a Pivot and add the SelectedDays field on the rows panel

11. Add the two Measured Values into the columns panel and click on update.

Download Mau Dau Solution.psm

Some businesses need to see the same results in a different currencies, for example a sales manager needs to see the same regional sales by different currencies for each region.

First, create a simple conversion table in Excel:

Now add this as a table in the ElastiCube manager. (You don't need to connect it to any other table)

Connect the Value picker to the Currency picker via the Dynamic Input such that every time you select a currency, the value changes.

Rename the Values picker to curr (Right click -> Rename)

Now here is the interesting part, you are going to use the Parameters feature to apply a measure calculation on the Pivot measure, To do so, click on the Pivot measure and edit the formula on the top:

[Measure] * [@curr] (Where [@curr] is our parameter)

Now every time the user selects a currency, the values picker changes and changes the pivot result.

Creating dashboards requires placeholders in many cases, in today's post I will show you some use cases for parameters

1. Get the products that did not sold on a selected date range:
   
   
   • First we need a date picker with date dimension and a pivot with products on the rows and some measure on the columns
   • Rename the Date Picker to Date1
     
   • Make sure that the date picker is not connected to the Pivot
   • Create a duplicate count aggregation on the Products call it # Products
     
   • Create a new custom measure (# Products, [@Date1]) and call it Selected Dates Products, this will result the number of products in the selected dates
   • Now it time to create a filter on the pivot, right click on the Product dimension and Filter -> Apply New Filter...
   • Select Filter by Measure and set Selected Dates Products > 0 then click on OK
     
     
2. Compare measure from two selected dates
   
   
   • Add two date pickers to the canvas and rename them to Date1 and Date2
     
   • Create a new Pivot and disconnect it from the date pickers
   • Add some dimension on the pivot and a measure you want to compare
   • Edit the Measure using placeholders i.e. (Measure, [@Date1]) then add the measure again and edit it i.e. (Measure, [@Date2]) 
   • Now you can start selecting different dates in the date pickers and see both measures changing according to the selected dates
   • Note: you can also combine placeholders: (Measure, [@Date1])/(Measure, [@Date2]) resulting the change between two selected dates

Today I will answer a very interesting question from Anthony.

Anthony need to know how many days pass from one sale to the other from a specific type of sale, lets say the sale type is [Code]  field and sale date is [DATE SOLD]

The table looks like that:

First I duplicated this table using the duplicate item when hovering a table, now we have two identical tables:[Daily Sales By Code] and [Daily Sales By Code2]

Next step is to Add an SQL statement that will combine this tables together:

SELECT a.[CODE],
       a.[DATE SOLD],
       b.[DATE SOLD],
       Daydiff(a.[DATE SOLD], b.[DATE SOLD]) diff
FROM   [Daily Sales By Code] a,
       [Daily Sales By Code2] b
WHERE  a.[CODE] = b.[CODE]
       AND a.[DATE SOLD] <> b.[DATE SOLD]
GROUP  BY a.[CODE],
          a.[DATE SOLD],
          b.[DATE SOLD] 

This will give us all permutations of the dates while its not the same date (a.[DATE SOLD] <> b.[DATE SOLD]) and both dates are from the same code  (a.[CODE] = b.[CODE]) I also added Daydiff(a.[DATE SOLD], b.[DATE SOLD]) to get the number of days between the date pairs.

This SQL statement will return the positive and negative date diff between the pairs so to get all dates with all positive Day Diffs we will wrap it by another sql statment:

SELECT *
FROM   (SELECT a.[CODE],
               a.[DATE SOLD],
               b.[DATE SOLD],
               Daydiff(a.[DATE SOLD], b.[DATE SOLD]) diff
        FROM   [Daily Sales By Code] a,
               [Daily Sales By Code2] b
        WHERE  a.[CODE] = b.[CODE]
               AND a.[DATE SOLD] <> b.[DATE SOLD]
        GROUP  BY a.[CODE],
                  a.[DATE SOLD],
                  b.[DATE SOLD]) t1
WHERE  t1.diff > -1   

Where  t1.diff > -1 will give us all positive date pairs.

Build and Sync the eCube, open Prism and calculate the Avg days between each sale from the same type:

Create a pivot with [DATE SOLD]on the rows and Min([Diff]) custom measure on the columns, add a combo with the [Code] dimension on it and you are done.

Note: To get the avg days between sales you can right click on the [Diff] header on the pivot select Sub Totals and Avg.

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.

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

Having a pivot table with data in it is not always enough. Sometimes, you want to display different colors for different value ranges.

In order to see coloring by values (Threshold), I will create a new custom string field [Indicator] in the ElastiCube Manager.

ifstring(([Amount]>50), 'Green', 'Red')

Now open PrismCubed, create a new Pivot and add Customer and Indicator dimensions on the rows and Amount measure on the columns.

Using the span selection on a member, select the Red member such that all Red members and Amount values will be selected and set its back color to red.
Do the same for the Green member.

Final step is to minimize the Indicator field such that it won't be visible.

We often receive questions from users who need to create special/custom fields. Here, I am happy to share with you the way you can convert an INT field [Seconds] into HH:MM:SS string format

First, we will need to add three custom INT fields (Hour, Min, Sec) to our table:

1. Hour Field = div([Seconds],3600)
   
   
2. Min Field  = div(Mod([Seconds],3600),60)
   
   
3. Sec Field = Mod([Seconds],60)

Finally, we will create a fourth field to contain the final HH:MM:SS string (HourRep). We do this by concatenating the three values, separated by colons (:) and we also include a '0' before any single-digit values. This is the code:

concat(
    concat(
        concat(
    -- Get hours indication depending on number of resulting digits.
    ifstring(([Hour]< 10), concat('0',ToString([Hour])),ToString([Hour])),':'),
        concat(
    -- Get minutes indication depending on number of resulting digits.
    ifstring(([Min]< 10), concat('0',ToString([Min])),ToString([Min])),':')),
    -- Get seconds indication depending on number of resulting digits.
    ifstring(([Sec]< 10), concat('0',ToString([Sec])),ToString([Sec])))

Good luck!

We just posted a new customer case study to the site, and since it is one of the coolest so far, I wanted to drop a word or two in our blog:

Roy Man, the CTO of saveanalien.com, has strong opinions about web analytics. He is also in the business of saving aliens.

Roy is one of the founders of a virtual alien (pet) site called saveanalien.com. Save an alien has 10 million unique aliens (each alien is one of a kind) and they are all up for adoption. The reason so many aliens are looking for a home is that "a meteor was heading towards their planet. They were going to die in 6 months if we didn't help..."

Aliens are flex clients developed with Ruby on Rails. The whole infrastructure runs on Amazon Web Services (EC2 and S3). The more care and virtual money earned by an alien-adopter, the better off the alien is – in terms of housing, furniture, looks, clothes etc.

In terms of Web Analytics, Roy has strong opinions. "We do the Web Analytics that everyone should do, although most sites don't". The philosophy is to constantly measure the value of each user, understand what they do and know what their lifetime value is. Roy is an avid SiSense Prism user: data is analyzed daily and then sent to company-wide distribution. "Without SiSense I would have needed to develop this myself". If he would have done this rather than use SiSense's common sense approach to business intelligence, he wouldn't have had time to save aliens.

Forrester research just published a report titled "BI Belt-tightening in a tough economic climate". In it Forrester Analyst Boris Evelson discusses the role of business intelligence in tough economic times.  

      "As an economic downturn becomes a sobering reality, enterprises look for various ways to increase revenues and reduce costs. While overall IT budgets become targets for cost cutting, business intelligence applications and infrastructure need not fall into the same category. Smart information and knowledge management professionals are leveraging BI as a corporate asset to continue to survive, compete and thrive…" 

There are two takeaway messages:

1. Don't panic: business intelligence is here to stay

2. Consider low cost alternatives to complement an existing BI stack 

One of the low cost alternatives to complement the existing enterprise BI stack is, of course, SiSense. We're very glad to be under Forrester's radar. 

Evelson's analysis of why small BI vendors are sometimes the better choice goes as follows: 

"Traditionally, mainstream BI vendors have catered primarily to IT target audiences. Even though these large vendors will disagree with such an IT-centric label, one look at their complex architectures, multiple layers and components, and integration and support requirements, reveals that these are indeed IT tools…. On the other hand, smaller BI vendors… pitch simplicity, flexibility, lower license cost, and little or no reliance on IT". 

He then lists the key things that small BI vendors need to offer:

• Data integration function to build an analytical data model
• Production/operational reporting for pixel perfect mass report distribution
• Ad hoc query tools for quick answers to business questions
• OLAP tools, for when business questions are more about the "whys" than the "whats"
• Dashboards for an interactive, visual UI – not reporting or analytical tool by itself
• SDKs

Congratulations to the team
Adi