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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.