Product Information: Data Manipulation & Mash-Up
Style Intelligence provides sophisticated capabilities for data manipulation and mash-up through its easy-to-use graphical Data Worksheets. With Worksheets, administrators and power-users can rapidly combine information from different data sources into meaningful and reusable Data Blocks.
The following example shows how to construct, manipulate, and save a new Data Block. For information on how to create more complex assets, please refer to the Data Worksheet Guide.
Constructing Data Blocks
There are many different ways you can construct Data Blocks. In this example, you will build a Data Block by pulling together several attributes from a single data model.
- In a Windows environment, open the Asset Composer from the option on the Windows Start Menu, Start → All Programs → Style Intelligence → Asset Composer.
- Click on the ‘New WorkSheet’ button in the top toolbar to open a new data worksheet.
- In the left pane of the Asset Composer, expand ‘Query’, then the ‘Orders’ data source. Also expand ‘Order Model’ and ‘Order’.
| #1 Ranking: Read how InetSoft was rated #1 for user adoption in G2's user survey-based index | Read More |
- Select and drag the ‘Date’ field from the tree to an empty cell on the data worksheet grid.
- This creates a new table with the title “Order,” the name of the parent entity from which the ‘Date’ field was drawn.
- Next, expand the ‘Product’ entity, and drag and drop the ‘Category’ attribute over the right side of the ‘Date’ column in the data worksheet. The table now includes both fields.
- Using the same procedure, add ‘Total’, from the ‘Product’ entity, to the data worksheet.
- Now, right-click the table title, and select ‘Properties’ from the context menu. This opens the ‘Table Properties’ dialog box.
- In the ‘Table Properties’ dialog box, rename the table by entering “MonthlySales” into ‘Name’ dialog box.
- Click ‘OK’ to close the dialog box. You have now created a “MonthlySales” data block that you can use in further data manipulations.
What Are the Various Ways a Data Analyst Can Manipulate Dates with InetSoft's StyleBI?
InetSoft’s StyleBI offers a robust set of tools for manipulating date and time data, enabling data analysts to create flexible, insightful, and time-aware dashboards and reports. Below are the various ways a data analyst can manipulate dates within StyleBI:
1. Date Hierarchies
- Automatically or manually build drillable time hierarchies (e.g., Year > Quarter > Month > Day).
- Use these hierarchies for drill-down analysis and time-based slicing.
- Ideal for dashboards with trend analysis over varying granularities.
2. Smart Date Parsing
- StyleBI recognizes and parses various date formats (ISO, US, EU).
- Auto-converts strings to date objects when applicable.
- Supports parsing from flat files, SQL, or REST APIs.
3. Date Filtering & Relative Time
- Filter data with absolute date ranges (e.g., Jan 1–Mar 31).
- Use relative filters like:
- Last N days (e.g., "Last 30 Days")
- This/Last/Next Month, Quarter, Year
- Custom expressions like "Today - 7"
- Analysts can build dynamic filters like:
text
CopyEdit
DATE >= CURRENT_DATE - 7
4. Date Grouping and Binning
- Group records by:
- Day, Week, Month, Quarter, Year
- Weekday/Weekend
- Custom fiscal periods
- Use custom binning (e.g., 5-day or 15-minute intervals).
- Useful for aggregating streaming or log data by uniform time slots.
5. Calculated Date Fields
- Create new date fields using built-in functions:
- YEAR(date), MONTH(date), DAY(date)
- DATEADD(date, interval)
- DATEDIFF(date1, date2)
- FORMAT(date, 'MMM yyyy')
- Example: Calculate days since signup:
text
CopyEdit
DATEDIFF(CURRENT_DATE, signup_date)
6. Time Series Analysis
- Apply moving averages, rolling sums, or comparisons across time.
- Perform YoY (Year-over-Year) or MoM (Month-over-Month) comparisons.
- Combine multiple time series in one chart using dynamic time alignment.
7. Trendlines & Forecasting
- Add trendlines over time in visualizations.
- Use exponential smoothing or linear regression to forecast future values.
- Highlight seasonality or time-based anomalies.
8. Fiscal Calendar Support
- Define custom fiscal calendars with non-standard year starts (e.g., fiscal year starting in October).
- Map dates to custom periods for quarterly business reporting.
9. Time Zone Adjustments
- Apply timezone conversions for global datasets.
- Normalize all timestamps to a common time zone for accurate aggregation.
- Great for SaaS products with international users.
10. Time-Aware Visualizations
- Use specialized charts:
- Timeline charts
- Gantt-style visualizations
- Sparkline trends with time on the x-axis
- Interactive tooltips show exact timestamps.
11. Data Source-Level Date Handling
- At the data source level:
- Preprocess or transform date fields in SQL queries.
- Use calculated columns or views to adjust formats/time zones.
- REST API sources can map date values into structured fields for parsing.
12. Conditional Formatting with Dates
- Highlight records based on date conditions:
- Show late tasks in red
- Highlight sales spikes in green if they occurred this week
- Apply rules like:
text
CopyEdit
IF order_date = CURRENT_DATE THEN highlight
Summary of Tools for Date Manipulation in StyleBI
Feature |
Description |
Date hierarchies |
Drill from year to day |
Relative date filters |
Last N days, this quarter, etc. |
Calculated fields |
Custom date math with DATEDIFF, DATEADD, etc. |
Grouping and binning |
Aggregate by week/month/custom interval |
Time series analysis |
Compare periods, detect trends |
Custom calendars |
Fiscal year handling |
Time zone normalization |
Align time data across regions |
Date-based formatting |
Conditional rules on dates |
| Previous: Virtual OLAP Models |
Next: Manipulate Data Blocks
|