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

Wed, 20 Apr 2016 03:02:45 GMT

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>