Set Analysis Expression
Set Analysis Expression
Set Analysis Expression
1 or more Filter :
Sum of two :
Ignore selection :
SUM (
{$<
[Order Date.autoCalendar.YearMonth] = ,
[Order Date.autoCalendar.Year] = { $(= Year ( Today() ) - 11 ) }
>}
Sales)
SUM (
{$<
>} Sales)
SUM (
{1<
[Order Date.autoCalendar.Year] = { $(= Year ( Today() ) - 11 ) }
>}
Sales)
YTD :
SUM (
{$<
>}
Sales)
SUM (
{$<
>}
Sales)
PYTD :
SUM (
{$<
>}
Sales)
Ignore Selection :
SUM (
{$<
[Order Date.autoCalendar.Year],[Order Date.autoCalendar.Month],[Order
Date.autoCalendar.Quarter],[Order Date.autoCalendar.Week],
[Order Date.autoCalendar.Date] = { ">=$(=Yearstart(Today(),-12) )
<=$(=Addyears(Today(),-12) )" }
>} Sales)
Total Sales :
SUM (
{$<
[Order Date.autoCalendar.Year],[Order Date.autoCalendar.Month],[Order
Date.autoCalendar.Quarter],[Order Date.autoCalendar.Week],
[Order Date.autoCalendar.Date] = { ">=$()<=$()" }
>}
Sales)
Variable Creation :
NUM(SUM (
{$<
[Order Date.autoCalendar.Year],[Order Date.autoCalendar.Month],[Order
Date.autoCalendar.Quarter],[Order Date.autoCalendar.Week],
[Order Date.autoCalendar.Date] = { ">=$(=Yearstart(Today(),-
12) )<=$(=Addyears(Today(),-12) )" }
>}
Sales),'#,##0')
QTD :
Incorrect :
SUM( {<
>}
Sales)
Incorrect :
SUM (
{$<
>} Sales)
// = { ">=$(=Yearstart(Today(),-11)) <= $(=Addyears(Today() ,-11)) " } -->>incorrect
Correct :
SUM (
{$<
>}
Sales)
Sum(
{<
Country = {'India','UK','USA'}
>} Sales)
Sum(
{<
Country = {"=SUM(Sales)>1000000"}
>}Sales)
Rank :
Sum(
{<
>}Sales)
Dynamic :
Sum(
{<
>}Sales)
YTD :
SUM(
{<
OrderDate = {">=$(vCurrentYearFirstDate)
<=$(vCurrentDate/MaxDate)"}
>}
[Sales Amount])
MTD :
SUM(
{<
OrderDate = {">=$(vCurrent Month/First Date) <=$(vCurrentDate/MaxDate)"}
>}
[Sales Amount])
PYMTD :
SUM(
{<
OrderDate = {">=$(vPrevCurrentMonth/First Date)
<=$(vPrevYearCurrentDate/MaxDate)"}
>}
[Sales Amount])
Expression :
=YearStart({1}max(OrderDate))
=Max({1}OrderDate)
=YearStart({1}max(OrderDate) ,-1)
=AddYears({1}Max(OrderDate),-1)
= MonthStart(Max({1}OrderDate))
= MonthStart(AddYears(Max({1}OrderDate),-1))
Outer Expression :
Inner :
Sum(
{<
Year = {'$(=Max(Year))'} , Category= {'Music'}
>}Sales)
Outer :
{<
Year = {'$(=Max(Year))'} , Category= {'Music'}
>}
Sum(Sales)
Inner :
Sum({<Year={'$(=Max(Year))'}>}Sales)/Count({<Year={'$(=Max(Year))'}>}distinct
OrderID)
Outer :
{<Year={'$(=Max(Year))'}>}Sum(Sales)/Count(distinct OrderID)
Outer 2 :
{<
Year = {'$(=Max(Year))'}
>}
Sum(${<Category= {'Music'}>}Sales)
{<
Year = {'(=Max(Year))'}
>}
Sum({<Category= {'Music'}>}Sales)
Max(Aggr(Sum(Sales),Country))
{<Quarter = {'Q2'}>}Max(Aggr(Sum({<Year={'$(=Max(Year))'}>}Sales),Country))
{<Category= {'Music'}>}
{<
Year = {'$(=Max(Year))'}
>}
Sum(Sales)
SUM
(
{<
Year = {2022},
Customer = P({<Product = {'Laptop'}>}Customer)
>} Sales )
SUM
(
{<
Year = {'$(=Max(Year))'},
Customer = P({<Product = {'Laptop'}>}Customer)
>}
Sales)
Part 2 :
SUM
(
{<
Year = {'$(=Max(Year) -1)'},
Customer = P({1<Product = {'Laptop'}>}Customer)
>}
Sales)
Count(
{<
Count(
{
<Customer = P({<Product = {'Laptop'} ,Year= {'$(=Max(Year))'}>})>
*
<Customer = P({<Product = {'Mobile'} ,Year= {'$(=Max(Year))'}>})>
} DISTINCT Customer)
// Customer = P({<>})
{<Year= {'$(=Max(Year))'}>}
SUM (
{
<Customer = P({<Product = {'Laptop'} ,Year= {'$(=Max(Year))'}>})>
*
<Customer = P({<Product = {'Mobile'} ,Year= {'$(=Max(Year))'}>})>
} Sales
)
PICK MATCH :
//SUM(1)
PICK
(
MATCH(DIM, 'Sales','Profit','Profit%'),
NUM(SUM(Sales),'#,##0'),NUM(SUM(Profit),'#,##0'),NUM(SUM(Profit)/SUM(Cost),'#,
##0.00%')
)
if(odd(ceil(RowNo(Total)/3)),$(vColor1))
Mapping Load :
Table_Name:
Mapping Load
FieldName_to_be_Mapped,
Desired_Mapping_Value
From SourceData;
Map_Table_Name:
Mapping Load
FieldName_to_be_Mapped,
Desired_Mapping_Value
From SourceData;