topic Re: Graph calculate days in QlikView

Graph calculate days

cmano — Sat, 09 Jul 2022 14:29:11 GMT

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.

Re: Graph calculate days

sidhiq91 — Sat, 09 Jul 2022 19:14:56 GMT

@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;

Re: Graph calculate days

MarcoWedel — Sun, 10 Jul 2022 16:34:57 GMT

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

Re: Graph calculate days

cmano — Mon, 18 Jul 2022 10:33:07 GMT

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.

Re: Graph calculate days

cmano — Mon, 18 Jul 2022 10:53:44 GMT

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

Re: Graph calculate days

PeggieP — Tue, 19 Jul 2022 04:43:19 GMT

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

Re: Graph calculate days

sidhiq91 — Tue, 19 Jul 2022 02:13:47 GMT

@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.

Re: Graph calculate days

sidhiq91 — Tue, 19 Jul 2022 02:17:04 GMT

@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.

Re: Graph calculate days

cmano — Tue, 19 Jul 2022 07:01:21 GMT

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

Re: Graph calculate days

MarcoWedel — Tue, 19 Jul 2022 20:16:46 GMT

"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/DateAndTimeFunctions/today.htm

Re: Graph calculate days

cmano — Thu, 21 Jul 2022 09:09:19 GMT

i cant figure out how i am supposed to write this function and where 😕