- Getting Started
- Administration Guide
-
User Guide
- An Introduction to Wyn Enterprise
- Document Portal for End Users
- Data Governance and Modeling
- Working with Resources
- Working with Reports
- Working with Dashboards
- View and Manage Documents
-
Understanding Wyn Analytical Expressions
- Syntax
- Measures and Calculated Columns
- Operators
- Functions
- Statements
-
Use Cases
- Use Case 1 - Actual Sales Profit
- Use Case 2 - Transaction Analysis per Customer
- Use Case 3 - Categorize fields based on calculations
- Use Case 4 - Cumulative Analysis
- Use Case 5 - RFM Model and Customer Value Analysis
- Use Case 6 - Correlation analysis
- Use Case 7 - Pareto analysis
- Use Case 8 - Difference analysis for any time period
- Use Case 9 - Sum by Product
- Best Practices
- Limitations
- Glossary
- Case...when Syntax
- Section 508 Compliance
- Subscribe to RSS Feed for Wyn Builds Site
- Developer Guide
Use Case 4 - Cumulative Analysis
Case 4: Cumulative Analysis
Requirement
Cumulative Analysis is generally used to do cumulative calculations.
Like:
Analysis of cumulative sales rate from Jan to Dec, or till present.
This year's sales completion rate, which is Sum(Cost) / Sum (Budget) for the whole year.
The sales completion rate until now, the result is Sum(Cost) / Sum (Budget) until now.
In this case, the cumulative value is the sum(SaleAmount), dimension is the year.
Steps for creating a dashboard
1. Drag a chart, drag OrderDate(Year) to the Axis.
2. Add a Measure on the table 'FactResellerSales'.
3. Input the expression given below and save it as MeasureCumulativeValue. So, the name of the Measure will be MeasureCumulativeValue.
CALCULATE(
SUMW(
SUMMARIZE(
CALCULATETABLE(
SELECTCOLUMNS(
FactResellerSales,
"FactResellerSales_OrderDate_Year",
Year(
'FactResellerSales'[OrderDate]
)
)
),
[FactResellerSales_OrderDate_Year],
"FactResellerSales_SalesAmount_SUMX",
CALCULATE(
SUMX(
CALCULATETABLE(
ADDCOLUMNS(
SELECTATTRIBUTES(
FactResellerSales,
'FactResellerSales'[SalesAmount]
),
"FactResellerSales_OrderDate_Year",
Year(
'FactResellerSales'[OrderDate]
)
)
),
'FactResellerSales'[SalesAmount]
)
),
"FactResellerSales_OrderDate_Year_Order_null",
CALCULATE(
ROWNUMBERW(
CALCULATETABLE(
DISTINCT(
SELECTCOLUMNS(
FactResellerSales,
"FactResellerSales_OrderDate_Year",
Year(
'FactResellerSales'[OrderDate]
)
)
)
),
OrderBy(
[FactResellerSales_OrderDate_Year]
)
)
)
),
[FactResellerSales_SalesAmount_SUMX],
OrderBy(
[FactResellerSales_OrderDate_Year_Order_null]
)
)
)
4. Drag the measure MeasureCumulativeValue to Values.
Explanation
CALCULATE(
SUMW(
SUMMARIZE(
CALCULATETABLE(
SELECTCOLUMNS(
FactResellerSales,
"FactResellerSales_OrderDate_Year",
Year(
'FactResellerSales'[OrderDate]
)
)
),
[FactResellerSales_OrderDate_Year],
"FactResellerSales_SalesAmount_SUMX",
CALCULATE(
SUMX(
CALCULATETABLE(
ADDCOLUMNS(
SELECTATTRIBUTES(
FactResellerSales,
'FactResellerSales'[SalesAmount]
),
"FactResellerSales_OrderDate_Year",
Year(
'FactResellerSales'[OrderDate]
)
)
),
'FactResellerSales'[SalesAmount]
)
),
"FactResellerSales_OrderDate_Year_Order_null",
CALCULATE(
ROWNUMBERW(
CALCULATETABLE(
DISTINCT(
SELECTCOLUMNS(
FactResellerSales,
"FactResellerSales_OrderDate_Year",
Year(
'FactResellerSales'[OrderDate]
)
)
)
),
OrderBy(
[FactResellerSales_OrderDate_Year]
)
)
)
),
[FactResellerSales_SalesAmount_SUMX],
OrderBy(
[FactResellerSales_OrderDate_Year_Order_null]
)
)
)Let us start with the outermost CALCULATE function.
The first parameter is required, and the filter is optional. In this case, we can remove calculate function.
Second, the SUMW function syntax is below:
SUMW(, [,PARTITIONBY([,[expression] [,...]])] [,ORDERBY( [(ASC|DESC)] [, [(ASC|DESC)] [,...] ] )] [,(ROWS|RANGE)( ((UNBOUNDED|) (PRECEDING|FOLLOWING) | CURRENT ROW) [,((UNBOUNDED|) (PRECEDING|FOLLOWING) | CURRENT ROW)])])In this case, the optional clause is only order by sub-function.
Consider that:
Parameter table: The first parameter is required. It can be a table or a table function, and the return value is a table. This parameter is very important and must contain columns of dimensions (for grouping), and values (which need to be calculated in the window function).
In the current case, a table is a function. We can see that it contains two values ("FactResellerSales_SalesAmount_SUMX", "FactResellerSales_orderDate_Year_Order_null") that need to be calculated in the window function, grouped by column (Year(OrderDate)).
The structure is as follows:
We can see that we use summarize function to generate a table. The first parameter is also a table (using table function), the second parameter is the dimension (need to group by), and the third, etc. parameter is the name and expression pair. The dimension name must follow the rule: if no need to extract datetime, the name is "tableName_columnName", if it needs to extract datetime, the name is "tableName_columnName_Type"， and the type here could be year , month, day, hour, minute, second. The dimension name is case-insensitive.
Parameter
The second parameter is required. Here it could be an "unqualified" name which is from the first parameter .
Parameter clause
This parameter is optional. But if we want to calculate the cumulative value, this parameter is needed. This is also an "unqualified" name which is from the first parameter .
The whole WAX is below:
EVALUATE
SELECTCOLUMNS(
SUMMARIZE(
CALCULATETABLE(
SELECTCOLUMNS(
FactResellerSales,
"FactResellerSales_OrderDate_Year",
Year(
'FactResellerSales'[OrderDate]
)
)
),
[FactResellerSales_OrderDate_Year],
"FactResellerSales_MeasureCumulativeValue_SUMX",
CALCULATE(
CALCULATE(
SUMW(
SUMMARIZE(
CALCULATETABLE(
SELECTCOLUMNS(
FactResellerSales,
"FactResellerSales_OrderDate_Year",
Year(
'FactResellerSales'[OrderDate]
)
)
),
[FactResellerSales_OrderDate_Year],
"FactResellerSales_SalesAmount_SUMX",
CALCULATE(
SUMX(
CALCULATETABLE(
ADDCOLUMNS(
SELECTATTRIBUTES(
FactResellerSales,
'FactResellerSales'[SalesAmount]
),
"FactResellerSales_OrderDate_Year",
Year(
'FactResellerSales'[OrderDate]
)
)
),
'FactResellerSales'[SalesAmount]
)
),
"FactResellerSales_OrderDate_Year_Order_null",
CALCULATE(
ROWNUMBERW(
CALCULATETABLE(
DISTINCT(
SELECTCOLUMNS(
FactResellerSales,
"FactResellerSales_OrderDate_Year",
Year(
'FactResellerSales'[OrderDate]
)
)
)
),
ORDERBY(
[FactResellerSales_OrderDate_Year]
)
)
)
),
[FactResellerSales_SalesAmount_SUMX],
ORDERBY(
[FactResellerSales_OrderDate_Year_Order_null]
)
)
)
),
"FactResellerSales_OrderDate_Year_Order_Auto",
CALCULATE(
ROWNUMBERW(
CALCULATETABLE(
DISTINCT(
SELECTCOLUMNS(
FactResellerSales,
"FactResellerSales_OrderDate_Year",
Year(
'FactResellerSales'[OrderDate]
)
)
)
),
OrderBy(
[FactResellerSales_OrderDate_Year]
)
)
)
),
"FactResellerSales_OrderDate_Year",
[FactResellerSales_OrderDate_Year],
"FactResellerSales_OrderDate_Year_Order_Auto",
[FactResellerSales_OrderDate_Year_Order_Auto],
"FactResellerSales_MeasureCumulativeValue_SUMX",
[FactResellerSales_MeasureCumulativeValue_SUMX]
)