8 Replies Latest reply: Jun 6, 2016 10:08 PM by lakshmi arayaa RSS

    Date Issue

    Vidhya Vk

      Hi all,

      I face a problem in Current Year and Previous Year numbers.

      Please find the attached word document for the same.

      Thanks in advance

        • Re: Date Issue
          Pulkit Thukral

          Possible way of calculating this could be (Only calculation part of set analysis written below) :

           

          Current Year current Month = Year={"$(=DATE(FLOOR(YearStart(max(Date))),'YYYY'))"},Month={"$(=DATE(FLOOR(MonthStart(max(Date))),'MMM'))"}

           

          Previous year Current Month =

          Month={"$(=date(AddMonths(DATE(FLOOR(MonthStart(max(Date))),'MMM'),-12),'MMM'))"}

           

          Current year YTD = Year={"$(=DATE(FLOOR(YearStart(max(Date))),'YYYY'))"}


          Previous year YTD= Year={"$(=DATE(FLOOR(YearStart(YearStart(max(Date))-1)),'YYYY'))"}

            • Re: Date Issue
              Vidhya Vk

              Thanks for you repli.

              But Date fields are mandatory in my expressions.

              Is there any work around with the expressions with "Date" fields??

                • Re: Date Issue
                  Pulkit Thukral

                  //Not Sure if we are setting year and month restriction in expression ,then why date restriction is //also required as we are comparing full month values and not particular days.


                  Current Year with Current Month

                  =num(sum({<Date={">=$(=DATE(FLOOR(YearStart(MAX(Date))),'MM/DD/YYYY'))<=$(=DATE(FLOOR(Yearend(MAX(Date))),'MM/DD/YYYY'))"}

                  ,FIN_MONTH_NO={"$(=DATE(FLOOR(MonthStart(MAX(Date))),'MMM'))"}>}Exit_Count)

                  /

                  ((sum({<FIN_YEAR_NO={"$(=DATE(FLOOR(YearStart(max(Date))),'YYYY'))"}

                  ,FIN_MONTH_NO={"$(=DATE(FLOOR(MonthStart(MAX(Date))),'MMM'))"}>}OpeningBalance)

                  +

                  sum({<FIN_YEAR_NO={"$(=DATE(FLOOR(YearStart(max(Date))),'YYYY'))"}

                  ,FIN_MONTH_NO={"$(=DATE(FLOOR(MonthStart(MAX(Date))),'MMM'))"}>}ClosingBalance))/2),'##.#%')

                   


                  Previous Year with Current Month

                  =num(sum({<Date={">=$(=DATE(FLOOR(YearStart(YearStart(max(Date))-1)),'MM/DD/YYYY'))<=$(=DATE(FLOOR(Yearend(YearStart(max(Date))-1)),'MM/DD/YYYY'))"}

                  ,FIN_MONTH_NO={"$(=date(AddMonths(DATE(FLOOR(MonthStart(max(Call_Date))),'MMM'),-12),'MMM'))"}>}Exit_Count)

                  /

                  ((sum({<FIN_YEAR_NO= {"$(=DATE(FLOOR(YearStart(YearStart(max(Date))-1)),'YYYY'))"}

                  ,FIN_MONTH_NO={"$(=date(AddMonths(DATE(FLOOR(MonthStart(max(Call_Date))),'MMM'),-12),'MMM'))"}>}OpeningBalance)

                  +

                  sum({<FIN_YEAR_NO= {"$(=DATE(FLOOR(YearStart(YearStart(max(Date))-1)),'YYYY'))"}

                  ,FIN_MONTH_NO={"$(=date(AddMonths(DATE(FLOOR(MonthStart(max(Call_Date))),'MMM'),-12),'MMM'))"}>}ClosingBalance))/2),'##.#%')

              • Re: Date Issue
                Krishnam Raju Dendukuri

                Try this .

                 

                Current Year = Year(Max(Date))

                 

                Current Month = month(Max(Date))

                 

                Previous Year = =Year(addmonths(max(Date) ,- 1))

                 

                Previous Month = Month(addmonths(max(Date) ,- 1))

                • Re: Date Issue
                  Sunny Talwar

                  Difficult to troubleshoot problems like these by just looking at images, would you be able to share a sample to look at?

                   

                  QlikCommunity Tip: How to get answers to your post?

                  Preparing examples for Upload - Reduction and Data Scrambling

                  Uploading a Sample

                  • Re: Date Issue
                    lakshmi arayaa

                    Current Year = Year(Max(Date))

                     

                    Current Month = month(Max(Date))

                     

                    Previous Year = =Year(addmonths(max(Date) ,- 1))

                     

                    Previous Month = Month(addmonths(max(Date) ,- 1))