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....
