0 Replies Latest reply: Apr 19, 2016 11:02 PM by Rachel McFarland RSS

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

    Rachel McFarland

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