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 … Continue reading Set Analysis in Qlikview

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’, … Continue reading Aggr

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   … Continue reading Qlikview Notes

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 ]; Continue reading Age in Year Month Day Format