Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

daisy1438
New Contributor

Urgent please help on this?

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.


7 Replies
undergrinder
Valued Contributor II

Re: Urgent please help on this?

Hi Daisy,

could provide me an example output?

G.

daisy1438
New Contributor

Re: Urgent please help on this?

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
Contributor

Re: Urgent please help on this?

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
Valued Contributor II

Re: Urgent please help on this?

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.




Khan_Mohammed
Honored Contributor II

Re: Urgent please help on this?

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
New Contributor

Re: Urgent please help on this?

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.

Khan_Mohammed
Honored Contributor II

Re: Urgent please help on this?

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?

Community Browser