Set Analysis in Qlikview

1.How to write this multiple if condition in set analysis?
if( days_affected >30 and Date < ’12/31/2009′, (Sum(Sales)*100),
if(days_affected >30 and Date > ’12/31/2009′,
(Sum(Sales)*2), Sum(Sales) )).

(Sum({30″},Date = {“} Sales)*100)
+(Sum({30″},Date = {“>$(=Date(Date#(’12/31/2009′,’MM/DD/YYYY’),’MM/DD/YYYY’))”}>} Sales)*2)
+if((days_affected >30 and Date 30 and Date > ’12/31/2009′),0,Sum(Sales))

2.How to write this in set analysis?

SUM(if(DATE>=DATE1 and DATE<=DATE2,{$} TABLE1.COMM * TABLE2.SAL))
SUM({$}if(DATE>=DATE1 and DATE<=DATE2,TABLE1.COMM * TABLE2.SAL))

or

Sum({<DATE={‘>=$(=DATE1)<=$(=DATE2)’}>} TABLE1.COMM * TABLE2.SAL)

3.How to write this in set analysis?

When FLAG = 1 and ORDERDATE – SHIPDATE > 0 then ORDERDATE – SHIPDATE)
Sum({} Aggr(If(SHIPDATE) < ORDERDATE, ORDERDATE- SHIPDATE) ),ORDER_ID))

If(ORDERDATE – SHIPDATE > 0, Sum({} ORDERDATE – SHIPDATE))

If(Only({} ORDERDATE – SHIPDATE) > 0, Sum({} ORDERDATE – SHIPDATE))
=count({0″}>}OrderID)

4.How to display Financial Year 2010/11, 2011/12, 2012/13?

Yearname(Date,0,4) as FinancialYear. It will display like 2011-2012,2010-2011..etc.

5.How to write set analysis?
Count the number of Task_ID based on the following conditions:
Completion_Date<= Due_Date
Source = Assignments or (Source=Tasks and TASK_TYPE = Product)

i) count(if((
Source=’Tasks’ and TASK_TYPE=’Product’ and Completion_Date<=Due_Date)
or
(Source=’Assignments’and Completion_Date<=Due_Date),Task_ID))

ii) Count(if(Completion_Date <= Due_Date
and (Source = ‘Assignments’
or (Source = ‘Tasks’ and TASK_TYPE = ‘Product’)),
Task_ID))

iii) If you add a unique ID field to your table (i.e. rowno() as ID) you can use a set analysis expression:

count({<Source={‘Assignments’},ID={“=Completion_Date<=Due_Date”}>+<Source={‘Tasks’},TASK_TYPE={‘Product’},ID={“=Completion_Date<=Due_Date”}>} Task_ID)

6.SUM({<EMPLYACNT={SALARY,T_BONUS,BONUS}>}if(TIMEID=’20110000′,SIGNEDDATA)) + SUM({<EMPLYACNT={R_MERIT,R_BONUS}>}IF(TIMEID=’20120000′,SIGNEDDATA))

=SUM({<EMPLYACNT={SALARY,T_BONUS,BONUS}, TIMEID={‘20110000’}>} SIGNEDDATA) + SUM({<EMPLYACNT={R_MERIT,R_BONUS}, TIMEID={‘20120000’}>} SIGNEDDATA)

7.How to exclude valueS from set analysis?

i) sum({$<EMP -={‘E1′,’E2’}>} Sales)

sum({$<EMP ={‘*’}-{‘E1′,’E2’}>} Sales)

ii) count({1<Art={‘Training’},Urlaub-={‘x’}>}SpielerID)

=count({1<Art={‘Training’},KEY =e({<Urlaub={‘x’}>+<verletzt = {‘x’}>+<krank = {‘x’}>}) >} DISTINCT SpielerID)

=count({1<Art={‘Training’},KEY =e({<Urlaub={‘x’}, verletzt = {‘x’}, krank = {‘x’}>}) >} DISTINCT SpielerID)

iii)=Sum({$<[Flag]-={‘x’}>}[Amount])

=Sum({$<[Flag] = e({<Flag = {‘x’}>})>}[Amount])

=Sum(Amount)-Sum({$<[Flag]={‘x’}>}[Amount])

=Sum({<SalesOrderID = {“=Flag <> ‘x'”}>} Amount)

=Sum({$<Flag ={‘*’}-{‘x’}>}[Amount])

iv)  

Sum({<CostoVen-={“*”}>} Sales)

sum({$-<CostoVen={‘*’}>} Sales)

sum({1-<CostoVen={*}>} Sales)

You could also make a null flag in your script.

If(Len(CostoVen) = 0, 1, 0) as NullFlag

Than your expression would look like this

sum({$<Nullflag={1}>} Sales)

v)

LOAD
*,
If(Len(Area_Name) = 0 OR IsNull(Area_Name), 1, 0) AS AreaFlag
FROM DataSource;

Now in expression just use this

=Sum({<AreaFlag={1}>} 1)
sum({$<Area_Name-={“=len(trim(Area_Name))=0”}>} Sales)

vi)Script:

Set vLastMonth=      “=Date(Max(Month),’MMM-YY’)”;

Set v2ndLastMonth= “=Date(AddMonths(Max(Month),-1),’MMM-YY’)”;

Date(MonthStart(Date),’MMM-YY’) as Month,

Expressions:

Sum( {$<Month={“$(vLastMonth)”}>} Amount )

Sum( {$<Month={“$(v2ndLastMonth)”}>} Amount )

8.Sum( {$< Fieldname1 = {“=IsNum(Left(Fieldname1,3)) = 0”} >} Fieldname2)

sum( {$< Fieldname1 = {“=IsNum(Left(Fieldname1,3))”} >} Fieldname2)

9.Sum of [A] + [B] when Division = ‘B’ and FY = vFY and if it does not, then get the Sum of [C] + [D].

sum({<Division={‘B’},FY={‘$(vFY)’}>} A+B)+sum({$-<Division={‘B’},FY={‘$(vFY)’}>} C+D)

Sum(If(Division=’B’ and FY=$(vFY), [A] + [B], [C]+[D]))

 

10.SELECT COUNT(1) FROM EMP_MASTR WHERE DATE_OF_JOING <= ’31-JUL-2015′ AND (QUIT_DATE IS NULL OR QUIT_DATE >= ’31-JUL-2015′)

Count({<EMPID = {“=QUIT_DATE >= MakeDate(2015, 7, 31) or Len(Trim(QUIT_DATE)) = 0”}>} EMPID)

Count({<DATE_OF_JOINING = {“<=2015-07-31”}, EMPLY_ID = {“=QUIT_DATE >= MakeDate(2015, 7, 31) or Len(Trim(QUIT_DATE)) = 0”}>} EMPLY_ID)

11.KPI Object – A MTD(Month To Date) set analysis to count no. of ACTIVE customers.

Condition of ACTIVE customer is – MUST HAVE at least 1 sales transaction within 3 months.

Example Scenario: suppose MTD Customer count period is 1 Jan 2017 – 31 Jan 2017. so, the no. of active of customer should count within this 3 months – Jan2017, Dec 2016, Nov2016.
Count(DISTINCT {<Year, Month, Quarter, Week, Month, DateNum = {‘>=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))’},
CUST_ID = {“=Count(DISTINCT {<Year, Month, Quarter, Week, Month,
DateNum = {‘>=$(=Num(MonthStart(Max(DateNum), -2)))<=$(=Max(DateNum))’}>} TRANSACTION_ID) > 0″}>} CUST_ID)

 12.TypeOfIncident=’Request’ && (IsSecondBreached =’True’ || IsThirdBreached=’True’ || IsResolutionBreached =’True’)

How can we convert the above condition to a set analysis statement?

Count({1<TypeOfIncident={‘Request’}>*(<IsSecondBreached ={‘True’}>+<IsThirdBreached={‘True’}>+<IsResolutionBreached ={‘True’}>)} Distinct [Counter Incident Number])

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

=sum(aggr(if(sum(sales) 100,1),store,ticket#))

if(isnull(Brandname),’OtherBrand’,Brandname) as Brandname,

or

if(len(Brandname)>0,Brandname,’OtherBrand’) as Brandname,

Sum({$Products= {“*”}-{“Other”} >} Sales)
or
sum(if((BrandName = ‘Shirts’ or BrandName=’Pants’ or BrandName=’Hats’ or ‘Shorts’) and SeasonShort =vCurrentSeason and Products’Other’ ,Sales)

Supress the null values:

Sum({$ {‘*’}>} PhaseTacklesPlayer)

=only({1}[Monthname]) >> It will return single possible value and ignore all the selections.

=Concat[Monthname]’,’) >> It will dispaly all the values

Count({$<CaseId= {“=Code ‘Test’ “}>}[Case Count])

Count({$<[CaseId] = e({} )>} [Case Count])

Advertisements

Aggr

Both are same:

Aggr( {<SetExpression>} Sum(X)/Sum(Y), Dim)

Aggr( Sum({<SetExpression>} X)/Sum({<SetExpression>} Y), Dim)

max(aggr(sum(Members)-above(Sum(Members)), (MonthYear,(NUMERIC, ASCENDING))))

  • Aggr( {<SetExpressionA>} Sum({<SetExpressionB>} X)/Sum(Y), Dim)
  • Aggr( Sum({<SetExpressionA>*<SetExpressionB>} X)/Sum({<SetExpressionA>} Y), Dim)

=If(GetSelectedCount(Client) = 1, Concat(Aggr(Rank(Sum({<Client = >} Sales)), Client), ‘,’), ‘Select a Single Client’)

=aggr(rank(TOTAL(sum({<Client=>}Sales))), Client)

 

 

sum({<Monthserial={$(=max(Monthserial))},                                 customer={“=sum({<Monthserial={‘>=$(=max(Monthserial)-5)<$(=max(Monthserial))’}>}Sales)=0”}>}
Sales)

 

sum({<Customer={“=sum(Sales)>=$(=max(aggr(sum(Sales),Customer),4))”}>}Sales)

=Aggr(If(Sum(Sales)>=Max(TOTAL Aggr(Sum(Sales), Customer), 4), Customer), Customer)

If(Aggr(Rank(Sum(Sales)),Customer)<=10,’A’,’B’)

concat({<Bowler = {‘=rank(sum(Wickets))=1’}>}distinct Bowler, ‘,’)

=AGGR(IF(ONLY(AGGR( NODISTINCT COUNT(DISTINCT Name), Project))=1, ‘Confidential’, Name), Project, Name)

 

If your Currency in dimension, try like:

=FirstSortedValue(Aggr(Sum(rates),Date), -Date)   // If multiple rates are there for a date

=FirstSortedValue(rates, -Date)                               // when sigle rate for a date

Edit: The first exp, probably should be like: =FirstSortedValue(Aggr(Sum(rates),Currency,Date), -Date)

=Aggr(NODISTINCT Rank(Sum(amount)),Person)

=aggr(if(rank(Random)   <=3,Person_ID),Person_ID)
=aggr(if(rank(Timestamp)<=5,Timestamp),Person_ID,Timestamp)

Sum(Aggr(FirstSortedValue(Amount,-Aggr(Max(TransDate),Prod,Cust,TransDate)),Prod,Cust))

 

Create a Pivot Table

Dimension

1) Year

2) Calculated Dimension

=IF(Aggr(Rank(SUM(Premium),4),Year,Claim No])<=3 or Aggr(Rank(-SUM(Premium),4),Year,[Claim No])<=3,[Claim No])

For both Dimensions Tick Suppress When Value is NULL

Expression

SUM({<Year = {‘$(=Year(Today()))’}>}Premium)

 

IF(DIMENSIONALITY()=1 OR DIMENSIONALITY()=2, sum(aggr(sum(Aggr(avg(SALES),STATE)),COUNTRY,REGION)),IF(DIMENSIONALITY()=3,sum(Aggr(avg(SALES),STATE)),sum(Aggr(Avg(SALES),CITY))))

Qlikview Notes

Only({<ZONE={‘A’}>} ZONE)
=count( {<code_t1=p([code_t2])>} DISTINCT id_t1)
=count(DISTINCT if(code_t1=code_t2, id_t1))

Count(DISTINCT {<sfdc_account_id = {‘*’}>} [account_uuid ])
count distinct only when sfdc_account_id is NOT NULL.
Count({$<OrderID = {“=Hour(ELS.FirstSubmission) >= Hour (MakeTime(18))”}>} OrderID )
Count({$<Hour_ELS.FirstSubmission = {“>=$(=Hour(MakeTime(18)))”}>} OrderID)

count( DISTINCT
{
<Customer_ID=P( {<Access_Week={$(=Access_Week)}>} Customer_ID)>*<Customer_ID=P( {<Access_Week={$(=Access_Week-1)} >} Customer_ID)>
}
Customer_ID)

 

c:

load if(Date_Closed=’null’,null(),Date_Closed) as Date_Closed, Record_ID inline [

Date_Closed ,    Record_ID

1/01/2015   ,       1

2/15/2015   ,       5

2/15/2015   ,       7

null        ,            10

null        ,            15

null        ,            15

];

and expression is

count({$ – <[Date_Closed]={‘*’}>} distinct [Record_ID])               result is 2

count({$ – <[Date_Closed]={‘*’}>} [Record_ID])                             result is 3

Custom Sort in Qlikview

Data:
LOAD * INLINE
[
Value, Sort order
Value A, 2
Value B, 1
Value C, 3
];

=match([Value], ‘Value B’, ‘Value A’, ‘Value C’)

PreLoad:
LOAD * INLINE
[
Value
Value B
Value A
Value C
];
Sales:
Load Customer, Value, Sales
From data;
Drop table PreLoad;

Age in Year Month Day Format

Copied from QV Community.

SET DateFormat=’DD/MM/YYYY’;

INPUT:

LOAD *

, if(day(VisitDay) >= day(DoB),

  mod(MonthIDVisitDay-MonthIDDoB,12),

  mod(MonthIDVisitDay-MonthIDDoB,12)-1) as AgeMonths

, if(day(VisitDay) >= day(DoB),

  day(VisitDay)-day(DoB),

  VisitDay-addmonths(DoB,12*AgeYears+mod(MonthIDVisitDay-MonthIDDoB,12)-1))+1 as AgeDays

;

LOAD *

, age(VisitDay,DoB) as AgeYears

, Year(DoB)*12+Month(DoB) as MonthIDDoB

, Year(VisitDay)*12+Month(VisitDay) as MonthIDVisitDay

INLINE [

DoB, VisitDay

01/01/2011, 11/03/2012

01/01/2010, 29/02/2012

01/03/2010, 29/02/2012

];