10 Replies Latest reply: Mar 14, 2017 6:29 AM by Agrim Sharma RSS

    how to count values whose interval difference is two months?

    Agrim Sharma

      Hi, All,

       

      i have an data in which, there are some user Accounts and its Last login(MM/DD/YYYY hh:mm:ss) Time & Last Logon(MM/DD/YYYY hh:mm:ss) dates

      i want to count such accounts that has interval difference of more than 2 months between Last login & Last Logon

      eg. Account    Last Logon                        Last login Time              Interval difference

      0109222J      6/18/2015 12:55:40 PM    2/21/2017 6:34:59 PM          614(days) or 20.467 Month

      0109227R    2/26/2017 1:00:15 PM        2/27/2017 1:00:15 PM          1(day) or 0.033 Month

       

      so i want "0109222J" as my desired output as it has interval difference more than 2 months

       

       

       

       

      my script is like this:-

       

      LOAD

          "Account",    

          "Last Logon",

            Role,

          "Last login Time",

          Status

       

       

      FROM [lib://AD Dashboard/All-AD-Accounts.Dashboard.xlsx]

      (ooxml, embedded labels, table is Sheet1);

      Thanks,

      Agrim

        • Re: how to count values whose interval difference is two months?
          Joe Easley

          Have you tried updating the number format on your field to 'MM hh:mm:ss'

          • Re: how to count values whose interval difference is two months?
            Massimo Grossi

            Using your 2 rows of test data

             

            1.png

             

             

            Logon:

            load * Inline [

            Account ,  Last Logon ,                      Last login Time,              Interval difference

            0109222J ,    6/18/2015 12:55:40 PM ,  2/21/2017 6:34:59 PM  ,        614(days) or 20.467 Month

            0109227R  ,  2/26/2017 1:00:15 PM    ,  2/27/2017 1:00:15 PM  ,        1(day) or 0.033 Month

            ];

             

            Final:

            load

              *,

              deltamonths > 2 as flag

              ;

            load

              Account,

              [Last Logon],                    

              [Last login Time],

              Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt') as t1,

              Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt') as delta,

              Floor(Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt')) as deltadays,

              Floor(Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 as deltamonths

            Resident Logon;

             

            DROP Table Logon;

              • Re: how to count values whose interval difference is two months?
                Agrim Sharma

                Hi Massimo,

                thanks for your kind help,

                can you tell me where should i place your provided script?

                my Script:-

                 

                 

                LOAD

                    "Account",

                    if ("Pre-W2K Name"  like 'User*' , 'USER',

                  if("Pre-W2K Name" like 'ADM*' , 'ADM',

                    if("Pre-W2K Name" like 'BAL*' , 'BAL',

                    if("Pre-W2K Name" like 'GEN*' , 'GEN',

                      if("Pre-W2K Name" like 'VC*' , 'VC',

                      if("Pre-W2K Name" like 'ADP*' , 'ADP',

                      if("Pre-W2K Name" like 'ROOM_*' , 'Room',

                      if("Pre-W2K Name" like 'FORMATION*' , 'FORMATION',

                  if("Pre-W2K Name" like 'SVC*', 'SVC'))))))))) as ACCOUNTGROUP,

                 

                 

                    if("Role" like 'DISABLED' , 'DISABLED',

                  if("Role" like 'DISABLED\NO_PWDEXP' , 'Disabled and Expired Password',

                    if("Role" like 'DISABLED\NO_PWDEXP\NO_PWDREQD' , 'Disabled, Password Expired and no password required',

                    if("Role" like 'NO_PWDEXP' , 'No Password Expired',

                      if("Role" like 'NO_PWDEXP\ENCRYPT' , 'Encrypted',

                        if("Role" like 'NO_PWDEXP\NO_PWDREQD' , 'No Password',

                        if("Role" like 'NO_PWDEXP\SMART' , 'Smart',

                 

                  if("Role" like 'NO_PWDEXP\DELEGATE', 'Delegated')))))))) as ACCOUNTSTATUS,

                    "Display Name",

                    "Last Logon",

                    "Exhange Alias",

                    "E-Mail",

                      "Lync User",

                    Role,

                    DN,

                    City,

                    Today(),

                    TextBetween( DN, 'CN=', ',') as [TypeName],

                    TextBetween( DN, 'OU=', ',') as [SiteName],

                    TextBetween( DN, 'DC=', ',') as DC,

                  

                 

                 

                    "Last login Time",

                    Status

                  

                 

                FROM [lib://AD Dashboard/All-AD-Accounts.Dashboard.xlsx]

                (ooxml, embedded labels, table is Sheet1);

                 

                where i have inserted Today(),


                what if want to get the output from

                Today() - "Last Logon" and if output comes more than 2 months

                then it will show only those accounts thats interval difference is more than 2 months between Today() and Last Logon

                  • Re: how to count values whose interval difference is two months?
                    Massimo Grossi

                    try to add a where filter on your data

                    maybe you have to change the format code, orange

                     

                    .....

                    .....

                    FROM [lib://AD Dashboard/All-AD-Accounts.Dashboard.xlsx]

                    (ooxml, embedded labels, table is Sheet1)

                    WHERE

                          Floor(Today() - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 > 2

                    ;   

                      • Re: how to count values whose interval difference is two months?
                        Agrim Sharma

                        ok thanks a lot for the help but can you please tell me where should i place your given script in my made script? my script is as follows:-

                         

                         

                        LOAD

                            "Pre-W2K Name", (Account)

                             if ("Pre-W2K Name"  like 'User*' , 'USER',

                           if("Pre-W2K Name" like 'ADM*' , 'ADM',

                            if("Pre-W2K Name" like 'BAL*' , 'BAL',

                             if("Pre-W2K Name" like 'GEN*' , 'GEN',

                              if("Pre-W2K Name" like 'VC*' , 'VC',

                              if("Pre-W2K Name" like 'ADP*' , 'ADP',

                              if("Pre-W2K Name" like 'ROOM_*' , 'Room',

                               if("Pre-W2K Name" like 'FORMATION*' , 'FORMATION',

                           if("Pre-W2K Name" like 'SVC*', 'SVC'))))))))) as ACCOUNTGROUP,

                         

                         

                             if("Role" like 'DISABLED' , 'DISABLED',

                           if("Role" like 'DISABLED\NO_PWDEXP' , 'Disabled and Expired Password',

                            if("Role" like 'DISABLED\NO_PWDEXP\NO_PWDREQD' , 'Disabled, Password Expired and no password required',

                             if("Role" like 'NO_PWDEXP' , 'No Password Expired',

                              if("Role" like 'NO_PWDEXP\ENCRYPT' , 'Encrypted',

                                if("Role" like 'NO_PWDEXP\NO_PWDREQD' , 'No Password',

                                if("Role" like 'NO_PWDEXP\SMART' , 'Smart',

                         

                           if("Role" like 'NO_PWDEXP\DELEGATE', 'Delegated')))))))) as ACCOUNTSTATUS,

                            "Display Name",

                            "Last Logon",

                            "Exhange Alias",

                            "E-Mail",

                               "Lync User",

                            Role,

                            DN,

                            City,

                            Today(),

                            TextBetween( DN, 'CN=', ',') as [TypeName],

                            TextBetween( DN, 'OU=', ',') as [SiteName],

                            TextBetween( DN, 'DC=', ',') as DC,

                          

                         

                         

                            "Last login Time",

                            Status

                          

                         

                        FROM [lib://AD Dashboard/All-AD-Accounts.Dashboard.xlsx]

                        (ooxml, embedded labels, table is Sheet1);

                          • Re: how to count values whose interval difference is two months?
                            Massimo Grossi

                            LOAD

                                "Pre-W2K Name", (Account)

                                 if ("Pre-W2K Name"  like 'User*' , 'USER',

                               if("Pre-W2K Name" like 'ADM*' , 'ADM',

                                if("Pre-W2K Name" like 'BAL*' , 'BAL',

                                 if("Pre-W2K Name" like 'GEN*' , 'GEN',

                                  if("Pre-W2K Name" like 'VC*' , 'VC',

                                  if("Pre-W2K Name" like 'ADP*' , 'ADP',

                                  if("Pre-W2K Name" like 'ROOM_*' , 'Room',

                                   if("Pre-W2K Name" like 'FORMATION*' , 'FORMATION',

                               if("Pre-W2K Name" like 'SVC*', 'SVC'))))))))) as ACCOUNTGROUP,

                             

                             

                                 if("Role" like 'DISABLED' , 'DISABLED',

                               if("Role" like 'DISABLED\NO_PWDEXP' , 'Disabled and Expired Password',

                                if("Role" like 'DISABLED\NO_PWDEXP\NO_PWDREQD' , 'Disabled, Password Expired and no password required',

                                 if("Role" like 'NO_PWDEXP' , 'No Password Expired',

                                  if("Role" like 'NO_PWDEXP\ENCRYPT' , 'Encrypted',

                                    if("Role" like 'NO_PWDEXP\NO_PWDREQD' , 'No Password',

                                    if("Role" like 'NO_PWDEXP\SMART' , 'Smart',

                             

                               if("Role" like 'NO_PWDEXP\DELEGATE', 'Delegated')))))))) as ACCOUNTSTATUS,

                                "Display Name",

                                "Last Logon",

                                "Exhange Alias",

                                "E-Mail",

                                   "Lync User",

                                Role,

                                DN,

                                City,

                                Today(),

                                TextBetween( DN, 'CN=', ',') as [TypeName],

                                TextBetween( DN, 'OU=', ',') as [SiteName],

                                TextBetween( DN, 'DC=', ',') as DC,

                             

                             

                             

                                "Last login Time",

                                Status

                             

                             

                            FROM [lib://AD Dashboard/All-AD-Accounts.Dashboard.xlsx]

                            (ooxml, embedded labels, table is Sheet1)

                            WHERE

                                  Floor(Today() - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 > 2

                            ;  


                              • Re: how to count values whose interval difference is two months?
                                Agrim Sharma

                                i do not have to include this? in my script?


                                Logon:

                                load * Inline [

                                Account ,  Last Logon ,                      Last login Time,              Interval difference

                                0109222J ,    6/18/2015 12:55:40 PM ,  2/21/2017 6:34:59 PM  ,        614(days) or 20.467 Month

                                0109227R  ,  2/26/2017 1:00:15 PM    ,  2/27/2017 1:00:15 PM  ,        1(day) or 0.033 Month

                                ];

                                 

                                Final:

                                load

                                  *,

                                  deltamonths > 2 as flag

                                  ;

                                load

                                  Account,

                                  [Last Logon],                   

                                  [Last login Time],

                                  Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt') as t1,

                                  Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt') as delta,

                                  Floor(Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt')) as deltadays,

                                  Floor(Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 as deltamonths

                                Resident Logon;

                                 

                                DROP Table Logon;

                                • Re: how to count values whose interval difference is two months?
                                  Agrim Sharma

                                  HI,

                                   

                                  i used this formula as a measure in a Pie Chart and it gives me output as (-1) and (0)

                                  (Floor(Date(Today()) - Date([Last login Time], 'dd-mm-yyyy hh:mm:ss'))/30 > 2)

                                  is there any way to replace (-1) as 'Delay'

                                  and (0) as 'On Time'