Qlikview , Sum sale of top N customer of each month

Using RANK function to check ranking of summary

Case1: Display rank with customer name for each month
Dimension1 = FactAll.MTH_YEAR
Dimension2 = Rank (Calculated Dim)
Dimension3 = CustomerName (Calculated Dim)

Dim2 :Rank
=Aggr(rank(sum(FactAll.Fee)),FactAll.MTH_YEAR ,Account.CustomerName)

Dim3 :CustomerName
=IF(AGGR(RANK(SUM(FactAll.Fee)),FactAll.MTH_YEAR, Account.CustomerName)<= 100 , Account.CustomerName )

Case2: Display only summary of top 100 customer fee
Dimension1 = FactAll.MTH_YEAR
Expression = SumFee

Measure Expression :
=SUM(
IF(Aggr(rank(sum(FactAll.Fee)),FactAll.MTH_YEAR ,Account.CustomerName)<=100,
Aggr( sum(FactAll.Fee) ,FactAll.MTH_YEAR ,Account.CustomerName))
)

Qlikview , Sum sale of top N customer of each month

Using RANK function to check ranking of summary

Case1: Display rank with customer name for each month
Dimension1 = FactAll.MTH_YEAR
Dimension2 = Rank (Calculated Dim)
Dimension3 = CustomerName (Calculated Dim)

Dim2 :Rank
=Aggr(rank(sum(FactAll.Fee)),FactAll.MTH_YEAR ,Account.CustomerName)

Dim3 :CustomerName
=IF(AGGR(RANK(SUM(FactAll.Fee)),FactAll.MTH_YEAR, Account.CustomerName)<= 100 , Account.CustomerName )

About order , Just order by rank 



Case2: Display only summary of top 100 customer fee
Dimension1 = FactAll.MTH_YEAR
Expression = SumFee

Measure Expression :
=SUM(
IF(Aggr(rank(sum(FactAll.Fee)),FactAll.MTH_YEAR ,Account.CustomerName)<=100,
Aggr( sum(FactAll.Fee) ,FactAll.MTH_YEAR ,Account.CustomerName))
)

Qlikview , Set analysis refer variable name / Filter with expression

='Y'&vMaxYear& ' : Total New Customer : ' &
NUM(SUM({<Dim_Month.Year={$(=vMaxYear) }>}NewCustomerCount) ,'#,##0')




ตัวอย่างการ Sum fee ของเดือนปัจจุบัน
SUM({<Calendar.MonthlyNum ={$(=DATE(NOW(),'YYYYMM'))}>} FactAll.Fee)



https://community.qlik.com/thread/80861

Correct Answerby jagan mohan rao appala on Apr 29, 2013 4:43 AM

Hi,

Try this expression

vDateMin = MonthStart(AddMonths(Max(Date), -12))

vDateMax = MonthEnd(AddMonths(Max(Date), -12))

=Sum({<YearDimensionName=, MonthDimensionName=, QuarterDimensionName=,Date = {">=$(=vDateMin)<=$(=vDateMax)"}>}$(vSales))

Exclude the date filters like Year, Month or Quarter selections if there are any.  Also check whether the date formats are same in Variable and Dimension.

Hope this helps you.

Qlikview , Ignore (Except) all sections but one!

=NUM(
COUNT({$<[$(=Concat({1<$Field-={'Team.TeamName','Customer.CustBrokerageFlag','Customer.CustWealthFlag','Customer.CustBTSFlag','Marketing.MktName','Customer.CustEmployeeFlag'}>} distinct $Field,']=,[')&']=')
,Customer.CustStatus={"Available"} > }  DISTINCT CustomerUID)
,'#,##0')




=COUNT(
{$<[$(=Concat({1<$Field-={'FactAll.YEAR'}>} distinct $Field,']=,[')&']=')
  >}
  DISTINCT Account.MGM_ReferGroupID)


FactAll.YEAR หมายความว่า Selection ที่เกิดกับฟิลด์นี้มีผล ก็คือ Exception ของการนับ

ส่วนตย.ด้านบนที่มี  ,Customer.CustStatus={"Available"} หมายถึงต้องการ Filter อย่างอื่นเพิ่ม

งงมาก Script นี้


มีอีกตัวอย่างที่ง่าย และน่าจะโอเค
https://community.qlik.com/thread/60630

sum({1<FieldToKeep=P(FieldToKeep)>}Fieldname)




************* ตัวอย่างนี้สุดยอด **************
การนับวันทำงานที่เหลือของปี หรือไม่ก็เวลาที่กำหนด

=IF(GetSelectedCount(Calendar.Year)=0 ,
COUNT(  
{$<[$(=Concat({1<$Field-={'Calendar.Year', 'Calendar.Month'}>} distinct $Field,']=,[')&']=') ,Calendar.WorkingDay={"Y"},Calendar.Year={$(=DATE(FLOOR(NOW()-1),'YYYY'))},Calendar.Date={">=$(=FLOOR(NOW()))"}  >} 
DISTINCT    Calendar.Date)
,
COUNT(  
{$<[$(=Concat({1<$Field-={'Calendar.Year', 'Calendar.Month'}>} distinct $Field,']=,[')&']=') ,Calendar.WorkingDay={"Y"} ,Calendar.Date={">=$(=FLOOR(NOW()))"} >} 
DISTINCT    Calendar.Date)


)




*************อันนี้ต่อ String Field เพื่อทำ Except บางอย่าง ************* 
ดูผลลัพธ์ใน Text แล้วคือ Field ทั้งหมดที่มี 
=Concat({1<$Field-={'Calendar.Year'}>} distinct $Field,']=,[')&']=' 

Qlikview , Dual (Chart) function , Multiple value on data point

Credit : https://community.qlikview.com/thread/166577

As a requirement , how to display value on data point in difference way.

Example you have bar chart contain sum of sale across month. Let's say you want Y axis to show the exactly of sale summary , But value on bar chart showing % of each month vs total.

Using DUAL : DUAL Function
DUAL(Text ,Number)

Example =
DUAL(
  NUM(SUM(Sales)/SUM( TOTAL Sales) ,'#,##0.00%'),
  SUM(Sales)
)


The another useful technic to formatting the axis
https://community.qlik.com/thread/97554
Summary : Create a dummy expression and then set invisibility and set the number format in number tab as you want. Incredible! Marvellous!

Qlikview , Round() Function , The amazing function from Qlik

As we know we usually us Round() function an particular script.
Our perception was Round is use to round only the decimal digit , But Qlikview is so difference!
Please read the manual.
Qlikview ROUND()

Qlikview , ValueLoop (Chart function)

Refer to :
https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/ChartFunctions/SyntheticDimensionFunctions/valueloop.htm

The great function for some advance technical.