8 Replies Latest reply on Jan 20, 2019 7:50 AM by Cristian Saavedra

    date1

    Satish G

      Hi Team,

       

      how to display first sunday of the month of hiredate?

        • 1. Re: date1
          Zhouyi Zhang

          Hi, Satish

           

          It should be something similar like below calculation:

          {Fixed datetrunc('month',[hiredate]:min(if datename('weekday',[hiredate]) = 'Sunday' then [hiredate] end))}

          If it doesn't work, pleaese provide sample workbook.

          ZZ

          • 2. Re: date1
            Cristian Saavedra

            Hi Satish,

             

            I attach an example of how to get it.

             

            I created a first sunday date as MAKEDATE(year([Hire Date]),MONTH([Hire Date]),IIF([SundayDate]==8,1,[SundayDate]))

             

            And SundayDate is 8-DATEPART("weekday",[Hire Date])+1

             

            Happy Viz!

            • 3. Re: date1
              Cristian Saavedra

              Hi,

               

              Interesting solution but the problem, It is that if the Hire Date is not exactly in a Sunday will return null:

               

              • 4. Re: date1
                Zhouyi Zhang

                Hi, Cristian

                 

                Yes, I understand. As mentioned, the solution might be various depending on what's data looks like, I just gave a possible solution.

                 

                ZZ

                • 5. Re: date1
                  Cristian Saavedra

                  If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                  • 6. Re: date1
                    Satish G

                    Hi Team,

                     

                    my requirement is if hiredate is 20-01-2019 it should display first sunday of the same month means 06-01-2019

                     

                     

                        

                    Employee IDEmployee NameSalaryOrganizationHire Date
                    ABM1506Satish G58548ABM Knowledgeware Ltd28-08-2014
                    WT84Soujanya V44100Wipro Technologies10-04-2013
                    PFT585Soujanya V38300Polaris Financial Technologies16-09-2011
                    IG5286Sailaja V23450Igate11-12-2013
                    A417Sailaja V28756Accenture21-05-2015
                    C526Muralidhar Reddy P70000Capita09-04-2012
                    A69Dhana Kumar M45780Accenture17-03-2015
                    NIIT453Mahaboob Basha M84000NIIT15-05-2015
                    ABM2144Anup Kumar Gopi30000ABM Knowledgeware Ltd01-07-2015
                    ABM702Nilesh Ramesh A33475ABM Knowledgeware Ltd15-01-2013
                    ABM1413Appayya Chowdary M61353ABM Knowledgeware Ltd27-02-2014
                    O158Uma Maheswara Rao P120000Oracle India Pvt Ltd02-01-2012
                    I199Sateesh P18521Infosys10-03-2015
                    ABM1325Madhu Babu G45610ABM Knowledgeware Ltd12-09-2013
                    SBI52Satya Surya Narayana G22451State Bank of India26-12-2013
                    BOB58Sujan Kumar C59561Bank of Baroda01-10-2014
                    VL153Devandra Kumar K45987Value Labs02-06-2015

                     

                    sample data

                    • 7. Re: date1
                      Zhouyi Zhang

                      Hi, Satish

                       

                      Please find my solution attached.

                      Below is the screenshot for your reference.

                       

                      Hope this helps

                      ZZ

                      1 of 1 people found this helpful
                      • 8. Re: date1
                        Cristian Saavedra

                        Sure, with the previous my post you can answer it.