Graph calculate days

[cmano]

Hello guys/girls.

I have the below table (example).

Fyi i have a serial number (unique order ) which have 2 codes ( 7 & 8). Each of the codes have a start and a planned end date.

Serial Number	Code	Start date	Planned End date	Current date
1	7	16-02-22	17-02-22	08-03-22
1	8	18-02-22	25-02-22	09-03-22
2	7	16-02-22	17-02-22	10-03-22
2	8	18-02-22	28-02-22	11-03-22
3	7	18-02-22	20-02-22	12-03-22
3	8	21-02-22	01-03-22	13-03-22

I need to make a graph (probably funnel ) to show the total time that needed for both codes and if is possible to read the current date and calculate current date in case codes exceeds planned end time.

Thanks for you time.

[sidhiq91]

@cmano  Please see the code below to calculate the total days required for both the codes.

NoConcatenate
Temp:
Load [Serial Number],
Code,
Date(Date#([Start date],'DD-MM-YY'),'MM/DD/YYYY') as [Start date],
Date(Date#([Planned End date],'DD-MM-YY'),'MM/DD/YYYY') as [Planned End date],
Date(Date#([Current date],'DD-MM-YY'),'MM/DD/YYYY') as [Current date]

Inline [
Serial Number,Code,Start date,Planned End date,Current date
1,7,16-02-22,17-02-22,08-03-22
1,8,18-02-22,25-02-22,09-03-22
2,7,16-02-22,17-02-22,10-03-22
2,8,18-02-22,28-02-22,11-03-22
3,7,18-02-22,20-02-22,12-03-22
3,8,21-02-22,01-03-22,13-03-22
];

NoConcatenate
Temp1:
Load [Serial Number],
sum(interval([Planned End date]-previous([Start date]),'DD')) as totalDays

Resident Temp
Group by [Serial Number];

Exit Script;

[MarcoWedel]

What is your question here? What part do you need help with?

[cmano]

Hello there.

I dont not understand. Do we have an automated way to collect the dates?
I have a big sheet of data.

Thank again for your time.

[cmano]

Hello @MarcoWedel .

Let me make a restatement

Serial Number	Code	Start date	Planned End date	Finished date
1	7	16-02-22	17-02-22	17-02-22
1	8	18-02-22	25-02-22	25-02-22
2	7	16-02-22	17-02-22	18-02-22
2	8	18-02-22	28-02-22	01-03-22
3	7	18-02-22	20-02-22
3	8	21-02-22	01-03-22

I have the above table and i want to make a chart (probably bar chart or similar).

The chart is gonna show how much time it was needed to complete for e.g serial number 1 code 1 & 2.

In general :
Serial number 1
Code : 1= 1 day
Code : 2= 7 day

More specific : To see the finished date and calculate the diff. If the finished day have no values to calculate the days by current date. 

Note:  Is any way if  the serial number do not have finished date (  and still pending ) to collect the date somehow by qlikview? e.g the day i make the report?

Thank you for your time

[PeggieP]

To find the number of days between these two dates, you can enter “=B2-B1” (without the quotes into cell B3). Once you hit enter, Excel will automatically calculate the number of days between the two dates entered.

official website

[sidhiq91]

@cmano  Did you try the code that I mentioned above, it gives the exact result that you are looking for. Calculate the numbers of the days on the service level. Please try and let me know what error you are getting.

[sidhiq91]

@PeggieP  Yes it is possible, at the back end you just have to a write a small piece of code, something like below:

interval([End date]-[Start date],'DD'). This is Similar as 'B2-B1' in excel.

[cmano]

Hello @PeggieP . 
changes on excel sheet

At excel sheet i have the below:

Planned Start Date & Completion Date.

Planned Start Date is filled with dates.

Completion Date in not until the code is completed.

So i have the below issue.

How qlikview can retrieve data dates if excel dont have one.

I mean i want ta make the report today for tomorrow presentation. Can qlikview take the current date i am gonna make the report and how?

I attached also the chart i wanna make

 

Thank you so much for your time

[MarcoWedel]

"Can qlikview take the current date i am gonna make the report and how?"

Yes:
 https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/DateAnd...