Normalized Graph

TIBCO Spotfire Community » TIBCO Spotfire » TIBCO Spotfire Clients » Normalized Graph

Last post Wed, Feb 10 2010 2:41 PM by martynp. 6 replies.

Page 1 of 1 (7 items)
	Sort Posts: Previous Next

Tue, Feb 9 2010 8:09 PM

martynp
Joined on Wed, Feb 10 2010
Posts 16

Normalized Graph

Reply Contact

I am trying to figure out how to crete a Calculated Column that will give me by item a months elapsed. So what I need is the Minimum Date - the Current Date. So if the first entry is 1/1/2009 the value will be 1 and the second entry 2/1/2009 will be 2 and so on. I know this is most likely utilizing the DateDiff function but it does not seem to work the way I thought it would.

Tue, Feb 9 2010 8:16 PM In reply to

martynp
Joined on Wed, Feb 10 2010
Posts 16

Re: Normalized Graph

Reply Contact

FYI - I have two Calculated Columns. The first, CC_FIRST_PROD_MONTH, is set to Min([PROD_DATE]). The second, CC_ELAPSED_MONTHS, is set to DateDiff('month',[CC_FIRST_PROD_MONTH],[CC_ELAPSED_MONTHS]). Both of these calucations work but the CC_FIRST_PROD_MONTH returns just the minimum value for all my items and not the minimum item value per item. How do I enable it to group on an item?

Tue, Feb 9 2010 10:48 PM In reply to

dathey
Joined on Mon, Dec 21 2009
Somerville, MA
Posts 10

Re: Normalized Graph

Reply Contact

To perform the initial calculation over a subset, try using the "OVER" keyword in the expression. For example, your first calculated column expression would look something like this:

Min([PROD_DATE]) OVER ([CATEGORY COL])

That will calculate the minimum "PROD_DATE" for each grouping using "CATEGORY COL" as the group identifier.

Dave

Filed under: custom expression

Wed, Feb 10 2010 9:00 AM In reply to

martynp
Joined on Wed, Feb 10 2010
Posts 16

Re: Normalized Graph

Reply Contact

Great this works. Now, how would I add a WHERE condition here. What I want is the MIN date based on one value from a table being greater than 0 (so the the first occurrence of a date when this value is > 0).

Wed, Feb 10 2010 9:37 AM In reply to

martynp
Joined on Wed, Feb 10 2010
Posts 16

Re: Normalized Graph

Reply Contact

I guess what I really need is to be able to have CC_FIRST_PROD_MONTH as a Calculated Column and not just a custom expression on the graph. I will need to filter based on CC_FIRST_PROD_MONTH. Is there a way to create a custom table where I just have the main primary key and this CC_FIRST_PROD_MONTH column?

Wed, Feb 10 2010 1:54 PM In reply to

dathey
Joined on Mon, Dec 21 2009
Somerville, MA
Posts 10

Re: Normalized Graph

Reply Contact

Conditional calculations can be performed using the "IF" function. For example, if you wanted to compute the MIN function only when a value for a particular column was above 0, it would look something like this:

IF ([MYCOL] > 0, Min([PROD_DATE]) OVER ([CATEGORY COL]), null)

Filed under: custom expression

Wed, Feb 10 2010 2:41 PM In reply to

martynp
Joined on Wed, Feb 10 2010
Posts 16

Re: Normalized Graph

Reply Contact

David, I now have this working perfectly. Thank you so much for your help!

Page 1 of 1 (7 items)

Spotfire's interactive information visualization and analytic solutions give users a remarkable experience for quickly and easily querying data and reporting results for superior business intelligence. From portfolio management and customer retention programs to key processes such as CRM, marketing, research, bioinformatics, yield and asset management and design for manufacturing, enterprises around the world rely on Spotfire's business analytics software to improve operational performance.