Urgent please help on this?

[daisy1438]

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.

[undergrinder]

Hi Daisy,

could provide me an example output?

G.

[daisy1438]

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 . 

[mithilesh17]

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

[undergrinder]

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.

[MK9885]

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))

[daisy1438]

Hi Shahbaz,

It is problem to drop the first table because of I am using first table for another kpis.

so without drop the first table how to write the expression.

and we need to some filter conditions like

Business=operations and status = open.

than load the id and date and targetdate fields and write the expressions.

[MK9885]

It doesn't really matter if you drop the 1st table cus in NewTable you'll have same data/values/fields.

Execute the script, test it and see if it doesn't work, you can roll back to old script?