Search results matching tag 'Calculated Columns'

Re: CALCULATED COLUMN / CUSTOM EXPRESSION TO CALCULATE A DIFFERENCE BTEWEEN TWO COLUMNS

benbeall — Thu, 14 Feb 2013 04:16:52 GMT

You can write a custom expression like this:

Sum(If([Year]=2012,[Sales Value)) AS [2012],
Sum(If([Year]=2013,[Sales Value)) AS [2013],
Sum(If([Year]=2013,[Sales Value)) - Sum(If([Year]=2012,[Sales Value)) AS [Difference]

You will not need [Year] in the top axis, only (Column Names)

Re: Displaying Quarters in analysis but is not included in the raw data

manoj_lanser — Mon, 14 Jan 2013 05:22:23 GMT

Hi Sajal, As soon as you put the date column in X-axis,For date column you can find Quarter(In-Built Function) ,Simply Select the function from Aggregation method.It will look like Quarter([Date Column]).If you want use this type of calculation ever where then simply make a calculated column Quarter([Date Column]). Regards Manoj

Dynamic crosstab with calculations on totals and conditional coloring of values in table

Conradwg — Sat, 12 Jan 2013 11:39:24 GMT

I am hoping someone can provide some assistance on how to handle moving a current analysis performed in Excel into Spotfire. The current Excel analysis pivots data (Product type, Product ID, Problem, Date) into a table. Date becomes the x-axis grouped by month and year. The y-axis utilizes Product Type, Product ID and Problem, with subtotals on Product Type and Product ID for each Problem. This part is easy to replicate in Spotfire with crosstabs. Problem #1 Once the data is pivoted in Excel, I have calculations performed to create separate control limits for each row. The calculations are average (Cbar), LCL - Lower Control Limit ([Cbar - 3*sqrt(Cbar)] and UCL - Upper Control Limit ([Cbar + 3*sqrt(Cbar)]. However, the calculations do not use the entire data set. The entire analysis contains 24 months of data. The average and control limits are calculated based on the first 18 months of data only. In Spotfire I know a new table can be created from the old that pivots the data. I am not sure how to perform multiple "total" calculations on only a subset of the data. Any recommendations?Problem #2 Once control limits are established for each row then each cell in that row of the pivot table is compared to those limits and conditionally colored (e.g. red if outside of control limits). I am not sure how to handle this in Spotfire. In excel it is done via VBA code.Problem #3 Control chart tests are then performed on each row, including the subtotals, of the Excel Pivot table. This includes whether or not the (1) last x months of values are above or below the average and (2) last x months of values are in increasing order or decreasing order. Indicators are placed in a column showing that a statistical trigger has occurred. This allows the data to be filtered to show only the rows that have statistical triggers. I am not sure how to do this in Spotfire.

This analysis has to be done in table format as opposed to actual control charts because there are 1000's of rows in the Pivot table. I am very interested in moving this into Spotfire so that filtering can be utilized in the visualization to dynamically control the content in the table. So the calculations noted above have to be able to update based on applied filters.

Re: Date difference excluding the weekends

michal.jakubik@miasolutions.sk — Wed, 02 Jan 2013 10:00:53 GMT

Hi Zubair,
yes - at the moment the function assumes Saturday and Sunday to be weekends. But it should not be a huge issue to change it (to Thursday and Friday - Arabic weekends?). Please let me know on michal.jakubik@miasolutions.sk and we will try to setup it as a separate deployment archive and I will let you know once it is ready for dowload.

If you would like to have it as a parameter in the method (with the working calendar), it would require some more custom development - will take some more time to develop and test it.
We can discuss this via E-Mail.

Thanks,
Michal

Re: Date difference excluding the weekends

michal.jakubik@miasolutions.sk — Sun, 30 Dec 2012 08:34:43 GMT

Hi Zubair,

not sure whether you solved your issue, but if you still need to solve this, have a look on http://www.miasolutions.sk/about-us/news/8-spotfire-extension-datetimediffwork

There you can (after registration) dowload the Spotfire Extension - Function that will solve your problems. You can exclude even the non-workingtime.

Cheers,

Michal

Re: How to update "Columns from External Data"?

Dheep — Wed, 21 Nov 2012 11:40:47 GMT

I haven't noticed that problem. Providing they are of the ame format you shuodl be able to match them? ALl my calculated columns were available.

I'm still waiting for Tibco support to come back with regards to my original comment.

Top 5 ,Top 10,Top 15 in a drop down

Mohanraj Subramanian — Thu, 18 Oct 2012 09:03:46 GMT

Hi Everyone,

Hi have a requirement like top 5, top 10 and top 20 in a drop down.And i have 5 Columns like sales,income,liabilities ,Expenses and Cashflow for every states.I have 5 different bar graph for every states what was my expenses ,cashflow, sales ,cost and liabilities.

is there any possibilities like if i select top 5 in the drop down i should see only the 5 states which is having maximum expenses,sales , cost etc in different graph simultanously.

thanks,

Performing a YTD Comparison on a Partial Year

Kevin Hanegan — Sat, 13 Oct 2012 19:36:00 GMT

One common question we see a lot is how to compare and analyze data from one year to the next. There are a variety of ways to do this. Let’s assume we have data like shown below, which includes transactional data of sales by sales rep and by region for 2011 and 2012.

If we want to analyze the results per region, for example, we could create a Bar Chart which shows Region on the Category-axis, and Sum ([Total]) on the Value-axis. We can additionally color by Year([Order Date]).

You will notice that the sales are all less in 2012, in some places significantly less. If we take a look at the raw data, we can see it is because the results for 2011 are complete, but at this given point in time, we are still in 2012, so we don’t have data through the end of the year.

What if we want to compare how we are doing so far this year compared to the same time period last year? Using a simple calculation, we can build a calculated column, called ‘Adjusted YTD’ which will show data only if the day of the year for 2011 is before today’s date in 2012.

if(DayOfYear([OrderDate])<=DayOfYear(DateTimeNow()),[Total],0)

If today is October 14, 2012, it will output sales totals for all rows in 2011 where the date is October 14,2011 or earlier, but for October 15th,2011, for example, it will output 0.

We can then update our Bar Chart to use the Adjusted YTD Calculated Column on the Value-axis, so that we can properly show an Actual YTD comparison.

If we now want to show the just a single bar which shows the difference between the Adjusted YTD this year versus last year, we can use the following custom expression on the Value-axis:

Sum([Adjusted YTD]) - Sum([Adjusted YTD]) OVER (Previous([Axis.Color]))

Interested in learning more about calculations and custom expressions in order to generate your own analysis? Please consider taking our SP141 Computational Analytics training course. This is offered onsite with a live instructor, and online, using our new Mentored Online Training format.

Performing a YTD Comparison on a Partial Year

Anonymous — Sat, 13 Oct 2012 19:36:00 GMT

if(DayOfYear([OrderDate])<=DayOfYear(DateTimeNow()),[Total],0)

If today is October 14, 2012, it will output sales totals for all rows in 2011 where the date is October 14,2011 or earlier, but for October 15th,2011, for example, it will output 0.

We can then update our Bar Chart to use the Adjusted YTD Calculated Column on the Value-axis, so that we can properly show an Actual YTD comparison.

If we now want to show the just a single bar which shows the difference between the Adjusted YTD this year versus last year, we can use the following custom expression on the Value-axis:

Sum([Adjusted YTD]) - Sum([Adjusted YTD]) OVER (Previous([Axis.Color]))

Alignment Of Values/Text in a Column

manoj_lanser — Thu, 04 Oct 2012 04:41:20 GMT

I have a column name as Total and it contains values like 2,7,10 but by default Spotfire 4.5 align them in Right.Is there any way to change the alignment to Center...?