Set Analysis Expression

Download as pdf or txt
Download as pdf or txt
You are on page 1of 11

QlikSense :

SUM({$<[Order Date.autoCalendar.Year] ={2012}>}Sales)

SUM ( {$ <[Order Date.autoCalendar.Year] = { $(= Year ( Today() ) ) } > } Sales )

SUM ( {$ <[Order Date.autoCalendar.Year] = { $(= Year ( Today() ) - 11 ) }, [Product


Category] = {'Furniture'} > } Sales )

1 or more Filter :

SUM ( {$ <[Order Date.autoCalendar.Year] = { $(= Year ( Today() ) - 11 ) }, [Product


Category] = {'Furniture'} , [Customer Segment] = {'Consumer'} > } Sales )

Sum of two :

SUM ( {$ <[Order Date.autoCalendar.Year] = { $(= Year ( Today() ) - 11 ) }> +


<[Customer Segment] = {"Consumer"} >}Sales)

Ignore selection :
SUM (
{$<

[Order Date.autoCalendar.YearMonth] = ,
[Order Date.autoCalendar.Year] = { $(= Year ( Today() ) - 11 ) }

>}
Sales)

Ignore all selection :

SUM (
{$<

[Order Date.autoCalendar.YearMonth] = , [Order Date.autoCalendar.Month] = ,


[Order Date.autoCalendar.Quarter] =,[Order Date.autoCalendar.Date] =,
[Order Date.autoCalendar.Year] = { $(= Year ( Today() ) - 11 ) }

>} Sales)
SUM (
{1<
[Order Date.autoCalendar.Year] = { $(= Year ( Today() ) - 11 ) }
>}
Sales)
YTD :

SUM (
{$<

[Order Date.autoCalendar.Date] = { ">=01/01/2012<=16/09/2012" }

>}
Sales)

SUM (
{$<

[Order Date.autoCalendar.Date] = { ">=$(=Yearstart(Today(),-11) )


<=$(=Addyears(Today(),-11) )" }

>}
Sales)

PYTD :
SUM (
{$<

[Order Date.autoCalendar.Date] = { ">=$(=Yearstart(Today(),-12) )


<=$(=Addyears(Today(),-12) )" }

>}
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( {<

[Order Date.autoCalendar.Date] = {">=$(=QuarterStart( Today () , -44)) <=


$(=ADDYEARS(Today() ,-11)) " }

>}
Sales)

Incorrect :

SUM (
{$<

[Order Date.autoCalendar.Date]= { ">=$(=Yearstart(Today(),-11))<=


$(=Addyears(Today() ,-11)) " }

>} Sales)
// = { ">=$(=Yearstart(Today(),-11)) <= $(=Addyears(Today() ,-11)) " } -->>incorrect

// = { ">=$(=Yearstart(Today(),-11) )<=$(=Addyears(Today(),-11) )" } --> Correct

Correct :
SUM (
{$<

[Order Date.autoCalendar.Date] = { ">=$(=QuarterStart( Today () , -


44) )<=$(=ADDYEARS(Today() ,-11) )" }

>}
Sales)

Set Analysis Function :

Sum(
{<

Country = {'India','UK','USA'}

>} Sales)
Sum(
{<

Country = {"=SUM(Sales)>1000000"}

>}Sales)

Rank :

Sum(
{<

Brand = {"=Rank(Sum(Sales)) <=10"}

>}Sales)
Dynamic :

Sum(
{<

Brand = {"=Rank(Sum(Sales)) <=$(vTop)"}

>}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)

sum({<Year = {'$(=Max(Year))'}>} Sales) - sum ({<Year = {'$(=Max(Year))'}>}Cost)

{<Year = {'$(=Max(Year))'}>} sum(Sales) - sum (Cost)

Max(Aggr(Sum(Sales),Country))
{<Quarter = {'Q2'}>}Max(Aggr(Sum({<Year={'$(=Max(Year))'}>}Sales),Country))

{<Category= {'Music'}>}
{<
Year = {'$(=Max(Year))'}
>}
Sum(Sales)

Indirect Set Analysis :

SUM( {<Product ={'Laptop'}>} Sales)

SUM( {<Product ={'Laptop'} , Customer={'AAA'} >} Sales)

SUM( {<Product ={'Laptop','TV'} >} Sales)

SUM( {<Product ={'Laptop','TV'}, Customer ={'AAA'} >} Sales)

Count({<Product = {'Laptop'}>} DISTINCT Customer)

CONCAT(DISTINCT Customer, ',')

Count({<Product = {'Laptop'}>} DISTINCT Customer)

CONCAT({<Customer = P({<Product = {'Laptop'}>}Customer)>}DISTINCT


Customer, ',')

Count({<Customer = P({<Product = {'Laptop'}>}Customer)>} DISTINCT


Customer)

SUM
(

{<
Year = {2022},
Customer = P({<Product = {'Laptop'}>}Customer)

>} Sales )
SUM
(
{<
Year = {'$(=Max(Year))'},
Customer = P({<Product = {'Laptop'}>}Customer)
>}
Sales)

Count({<Customer = E({<Product = {'Laptop'}>}Customer)>} DISTINCT


Customer)

Part 2 :

Count({<Customer = P({1<Product = {'Laptop'}>}Customer)>} DISTINCT


Customer)

SUM
(
{<
Year = {'$(=Max(Year) -1)'},
Customer = P({1<Product = {'Laptop'}>}Customer)
>}
Sales)

Count({<Customer = P({<Product = {'Laptop','Mobile'}>}Customer)>} DISTINCT


Customer)

Count(
{<

Customer = P({<Product = {'Laptop','Mobile'} ,Year= {'$(=Max(Year))'}>}Customer)

>} DISTINCT Customer)


Intersection :

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))

IF( odd(RowNo(total)) , $(vColor1) )

Mapping Load :

Table_Name:

Mapping Load
FieldName_to_be_Mapped,
Desired_Mapping_Value
From SourceData;

ApplyMap('Mapping_Table_Name', MapFieldName, DefaultValue) as FieldName

Map Field Name Using :

Map_Table_Name:
Mapping Load
FieldName_to_be_Mapped,
Desired_Mapping_Value
From SourceData;

Map FieldName using Map_Table_Name;


Unmap FieldName;

Unmap FieldNamet1, FieldName2, FieldName3;


Unmap *;

You might also like