Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Set Analysis (using from and to dates) with AGGR function

Hello, I am having trouble with this scenario. I have working code that I am using to get Working Days in the month weighted per branch (by sales $) as the branches are in different states with different public/bank holidays. I then tried to adjust that code on a different chart that has set analysis that lets the user compare two date ranges by selecting Start and End dates per period (called Primary and Secondary). However, I am not sure how to convert the Financial Year, Financial Month part into the Start and End Dates correctly.

Please refer to code below.

SUM(

{<SalesPostDate={">=$(vSVStartDateP1Format)<=$(vSVEndDateP1Format)"}, MonthYear=, FinancialYear= , FinancialMonth= ,CalendarYear= ,Day= ,Quarter= >}

  AGGR(

  (SUM(

  {<SalesPostDate={">=$(vSVStartDateP1Format)<=$(vSVEndDateP1Format)"}, MonthYear=, FinancialYear= , FinancialMonth= ,CalendarYear= ,Day= ,Quarter= >}

  IF(vRevenueTypeId =1, IF(vIncGST = 0, RawPreGSTRevenue + IF(vIncBookmark = 1,PreGSTBookmarked,0)  + IF(vIncCredit = 1,PreGSTCredit,0) + IF(vIncFuel = 1,PreGSTSurcharge,0) + + IF(vIncWarranty = 1,PreGSTWarranty,0)

   ,RawTotalRevenue + IF(vIncBookmark = 1,TotalBookmarked,0)  + IF(vIncCredit = 1,TotalCredit,0) + IF(vIncFuel = 1,TotalSurcharge,0) + IF(vIncWarranty = 1,TotalWarranty,0)),

  IF(vRevenueTypeId = 3,  IF(vIncGST = 0, PreGSTBookmarked + IF(vIncFuel = 1,PreGSTSurchargeBookmarked,0) + + IF(vIncWarranty = 1,PreGSTWarrantyBookmarked,0)

  ,TotalBookmarked + IF(vIncFuel = 1,TotalSurchargeBookmarked,0) + IF(vIncWarranty = 1,TotalWarrantyBookmarked,0)),

  IF(vRevenueTypeId = 4, IF(vIncGST = 0, PreGSTCredit + IF(vIncFuel = 1,PreGSTSurchargeCreditExTCredit,0) + + IF(vIncWarranty = 1,PreGSTWarrantyCreditExTCredit,0)

  ,TotalCredit + IF(vIncFuel = 1,TotalSurchargeCreditExTCredit,0) + IF(vIncWarranty = 1,TotalWarrantyCreditExTCredit,0)),

  IF(vRevenueTypeId = 5, IF(vIncGST = 0, PreGSTSurcharge + IF(vIncBookmark = 1,PreGSTSurchargeBookmarked,0)  + IF(vIncCredit = 1,PreGSTSurchargeCreditExTCredit,0)

    ,TotalSurcharge + IF(vIncBookmark = 1,TotalSurchargeBookmarked,0)  + IF(vIncCredit = 1,TotalSurchargeCreditExTCredit,0)),

  IF(vRevenueTypeId = 6,RawGSTRevenue + IF(vIncBookmark = 1,GSTBookmarked,0)  + IF(vIncCredit = 1,GSTCredit,0) + IF(vIncFuel = 1,GSTSurcharge,0) + + IF(vIncWarranty = 1,GSTWarranty,0),

  IF(vRevenueTypeId = 7,IF(vIncGST = 0, PreGSTWarranty + IF(vIncBookmark = 1,PreGSTWarrantyBookmarked,0)  + IF(vIncCredit = 1,PreGSTWarrantyCreditExTCredit,0)

  ,TotalWarranty + IF(vIncBookmark = 1,TotalWarrantyBookmarked,0)  + IF(vIncCredit = 1,TotalWarrantyCreditExTCredit,0)),0))))))

  )

  / SUM(

  {<SalesPostDate={">=$(vSVStartDateP1Format)<=$(vSVEndDateP1Format)"}, MonthYear=, FinancialYear= , FinancialMonth= ,CalendarYear= ,Day= ,Quarter= >}TOTAL<{<SalesPostDate={">=$(vSVStartDateP1Format)<=$(vSVEndDateP1Format)"},ACWWeightBreakName>

  IF(vRevenueTypeId =1, IF(vIncGST = 0, RawPreGSTRevenue + IF(vIncBookmark = 1,PreGSTBookmarked,0)  + IF(vIncCredit = 1,PreGSTCredit,0) + IF(vIncFuel = 1,PreGSTSurcharge,0) + + IF(vIncWarranty = 1,PreGSTWarranty,0)

   ,RawTotalRevenue + IF(vIncBookmark = 1,TotalBookmarked,0)  + IF(vIncCredit = 1,TotalCredit,0) + IF(vIncFuel = 1,TotalSurcharge,0) + IF(vIncWarranty = 1,TotalWarranty,0)),

  IF(vRevenueTypeId = 3,  IF(vIncGST = 0, PreGSTBookmarked + IF(vIncFuel = 1,PreGSTSurchargeBookmarked,0) + + IF(vIncWarranty = 1,PreGSTWarrantyBookmarked,0)

  ,TotalBookmarked + IF(vIncFuel = 1,TotalSurchargeBookmarked,0) + IF(vIncWarranty = 1,TotalWarrantyBookmarked,0)),

  IF(vRevenueTypeId = 4, IF(vIncGST = 0, PreGSTCredit + IF(vIncFuel = 1,PreGSTSurchargeCreditExTCredit,0) + + IF(vIncWarranty = 1,PreGSTWarrantyCreditExTCredit,0)

  ,TotalCredit + IF(vIncFuel = 1,TotalSurchargeCreditExTCredit,0) + IF(vIncWarranty = 1,TotalWarrantyCreditExTCredit,0)),

  IF(vRevenueTypeId = 5, IF(vIncGST = 0, PreGSTSurcharge + IF(vIncBookmark = 1,PreGSTSurchargeBookmarked,0)  + IF(vIncCredit = 1,PreGSTSurchargeCreditExTCredit,0)

    ,TotalSurcharge + IF(vIncBookmark = 1,TotalSurchargeBookmarked,0)  + IF(vIncCredit = 1,TotalSurchargeCreditExTCredit,0)),

  IF(vRevenueTypeId = 6,RawGSTRevenue + IF(vIncBookmark = 1,GSTBookmarked,0)  + IF(vIncCredit = 1,GSTCredit,0) + IF(vIncFuel = 1,GSTSurcharge,0) + + IF(vIncWarranty = 1,GSTWarranty,0),

  IF(vRevenueTypeId = 7,IF(vIncGST = 0, PreGSTWarranty + IF(vIncBookmark = 1,PreGSTWarrantyBookmarked,0)  + IF(vIncCredit = 1,PreGSTWarrantyCreditExTCredit,0)

  ,TotalWarranty + IF(vIncBookmark = 1,TotalWarrantyBookmarked,0)  + IF(vIncCredit = 1,TotalWarrantyCreditExTCredit,0)),0))))))

  )

  )

  * MAX({<SalesPostDate={">=$(vSVStartDateP1Format)<=$(vSVEndDateP1Format)"}, MonthYear=, FinancialYear= , FinancialMonth= ,CalendarYear= ,Day= ,Quarter= >}TotalDaysInMonth)

  ,ACWWeightBreakName,InvoiceBranchName

  )

  )

Hoping someone can give me some guidance....

Community Browser