7 Replies Latest reply: Aug 9, 2018 8:53 AM by Shahbaz Khan Mohammed RSS

    Urgent please help on this?

    daisy ch

      Hi

      target date changed count the ID's.


      I have table fields

      table:

      Issu_ID

      date_upload

      Taget_date



      -> KPI chart if the user selects any two dates compare those two dates.if maximum date and maximum previous dates in the Taget_date field are different then caluclate the count id's.



      Example:


      Issue_ID      Date_upload      Target_date

       

      01                01-01-2018           10-05-2018

      01                03-01-2018           15-05-2018

      02                01-01-2018           10-05-2018

      02                03-01-2018           10-05-2018

      03                01-01-2018           10-05-2018

      03                03-01-2018           16-05-2018



      In the above table if we select dates in the date upload field i.e., 01-01-2018 and 03-01-2018 then count is 2 because id 01,03 target dates are changed and id 02 target dates are not changed.

       

       

      so just count the Issues_ID when user select any two dates(ex:01-01-2018 is min date and 03-01-2018 is max date)compare those two dates if min date targetdate is onedate and max date targetdate is change to another date than caliculate the Issues_ID count.



      I need quires for the above requirements in the back-end level.and write the expression front end level also if any simple way.

      I have written quires in the front end level but the performance is slow and it is showing an error in the chart i.e., time out.

                              


      Thanking you.


       

        • Re: Urgent please help on this?
          Gabor Tarnoczai

          Hi Daisy,

           

          could provide me an example output?

           

          G.

            • Re: Urgent please help on this?
              daisy ch

              Hi

               

              Example : you have  Id and date and Targe_tdate.

              Assume in date field having 3 months dates.01-jan -2018 to 31-Mar-2018.

              If user select any two dates in this 3 months dates example  10-jan -2018 and 20-feb-2018 compare those two dates 10 and 20 not in-between dates only 10 and 20 and compare those two dates. if date 10-jan-2018 target date is one 20-feb-2018 targetdate changes than count the the IDs.

              it means what are the id's target date change in those two days than count the ids . 

                • Re: Urgent please help on this?
                  Gabor Tarnoczai

                  Thanks, but it is still confusing.

                   

                  So this the base data set:

                  Issue_ID      Date_upload      Target_date

                  01                01-01-2018           10-05-2018

                  01                03-01-2018           15-05-2018

                  02                01-01-2018           10-05-2018

                  02                03-01-2018           10-05-2018

                  03                01-01-2018           10-05-2018

                  03                03-01-2018           16-05-2018


                  When I select in Date_upload a date, like 01-01-2018 what is the desired output?

                  Count of ID (=3)? What means the duplicate ID?


                  Could you provide a simple example based on your table with inputs - outputs?


                  G.




              • Re: Urgent please help on this?
                Mithilesh Bhoir

                Hi,

                 

                Get those two values of date in two variables and use it in expression for date range, like you must have used in MTD and YTD.

                 

                Regards,

                Mithilesh

                • Re: Urgent please help on this?
                  Shahbaz Khan Mohammed

                  Not sure if this would work....

                  YourTable:

                  Load

                  Issue_ID,

                  Targent_date,

                  Date_upload

                  From.......;

                   

                  New_Tabel:

                  Load*,

                  Issue_ID as IssueID,

                  if(Target_date<>peek(Target_date), Previous(Issue_ID)) New_IssueID

                  resident YourTable

                  Order by Issue_ID;

                  Drop  table YourTable;

                   

                   

                  and in front end use expression as

                  Count(distinct(New_IssueID))