4 Replies Latest reply: Apr 11, 2017 7:12 AM by Mike E RSS

    AddMonths syntax with set analysis

    Mike E

      Hi Fellow Qlikers,

       

      I have the following code which works as expected:

       

      COUNT ({<DateReconciled = {'>=$(vStartDate)'},TransactionTypeID = {1}, IsReversal = {0}, SaleYear=, SaleMonth=, SaleYearMonth=, SaleDate=>} NetValue)

       

      However I want to obtain 6 months  prior i.e Addmonths (x, -6)  as follows but no joy...

       

      COUNT ({<EstimatedDate = {'>=AddMonths($(vStartDate),1)'},TransactionTypeID = {1}, IsReversal = {0}, SaleYear=, SaleMonth=, SaleYearMonth=, SaleDate=>} NetValue)

       

       

      Can anyone see an obvious error here as I cannot?

       

      Thanks  & Kind Regards

      Mike

        • Re: AddMonths syntax with set analysis
          Sunny Talwar

          Try this

           

          Count({<EstimatedDate = {'>=$(=AddMonths($(vStartDate),1))'},TransactionTypeID = {1}, IsReversal = {0}, SaleYear=, SaleMonth=, SaleYearMonth=, SaleDate=>} NetValue)

            • Re: AddMonths syntax with set analysis
              Mike E

              Hi Sunny,

              Thanks for your reply. This  modification shows ALL the data within the date range i.e. no  filter is applied which is better than my nothing but not quite what I want given the data  goes back to June 2000 and I am showing month by month payments. Any further ideas?

              Thanks

              Mike

                • Re: AddMonths syntax with set analysis
                  Sunny Talwar

                  Within the date range? What is your date range? May be try this

                   

                  Count({<EstimatedDate = {'>=$(=AddMonths($(vStartDate),-6))<=$(=$(vStartDate))'},TransactionTypeID = {1}, IsReversal = {0}, SaleYear=, SaleMonth=, SaleYearMonth=, SaleDate=>} NetValue)

                   

                  Does this give you what you want? If not, then please share more information about the format or EstimatedDate and what is vStartDate? Can you share its definition? Also, look here

                  Dates in Set Analysis

              • Re: AddMonths syntax with set analysis
                Mike E

                Hi Sunny,

                 

                I have found it I need to remove the extra $ as follows:

                 

                Count({<EstimatedDate = {'>=$(=AddMonths( $ (vStartDate),1))'},TransactionTypeID = {1}, IsReversal = {0}, SaleYear=, SaleMonth=, SaleYearMonth=, SaleDate=>} NetValue)


                i.e. the one before (vStartdDate)  with a dash crossed out in the example above.

                Many thanks for your help

                Mike