3 Replies Latest reply: Sep 20, 2016 6:48 AM by Shazad Nazir RSS

    Comparing two date fields in set analysis

    Shazad Nazir

      Hi Experts,

       

      I am having problem in following expression: i.e. comparing two data fields along with one account code field.

      1. DocDate
      2. BankReconDate

       

      sum({<Account = {'A1012304001'},DocDate <= BankReconDate>} ExtrCredit)

       

      Help required please!

        • Re: Comparing two date fields in set analysis
          Sunny Talwar

          May be try this:

           

          Sum({<Account = {'A1012304001'}>} If(DocDate <= BankReconDate, ExtrCredit))

           

          or

           

          Sum({<Account = {'A1012304001'}>*<Account = {"=DocDate <= BankReconDate"}>} ExtrCredit)

           

          or

           

          Sum({<Account = {"=Account = 'A1012304001' and DocDate <= BankReconDate"}>} ExtrCredit)

            • Re: Comparing two date fields in set analysis
              Shazad Nazir

              Actually I am trying to compare two fields (in set analysis) with each other, not having two fields in set analysis.

              For that matter I used the following options, it does not work

               

              1 .Sum(If(SOPostingMonth <> DOCreateMonth), RowTotalSO))

              2. Sum({<DocDate -= DOCreateDate>} RowTotalSO)

               

              I want to have summary of only those Sales Orders value whose date's months do not match with each other's

               

              I also tried using the variables: but does not work

               

              1. Sum(If(SOPostingMonth <> DOCreateMonth), RowTotalSO))

            • Re: Comparing two date fields in set analysis
              Shazad Nazir

              In continuation to my previous post. I am looking forward to compare two fields with each other instead of having two fields in set analysis. i.e. Month(DocDate) is not equal to Month(DOCreateDate)

              Just pasting the data in tabular form as follows:

              summary is just required for those whose Month(DocDate) is not equal to Month(DOCreateDate).

               

               

              Month(DocDate)Month(DOCreateDate)Year(DocDate)Sum(RowTotalSO)
              FebApr201611,402,350
              FebMar201635,174,450
              FebFeb201662,395,025