Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a excel data source containing data in below format.
Job ID | Job Type | Job Start Date | End Date | Status |
---|---|---|---|---|
1 | A | 23-01-16 | 01-02-16 | Started |
2 | B | 25-01-16 | 01-02-16 | Ended |
3 | B | 28-01-16 | 01-02-16 | Paused |
4 | A | 01-02-16 | 01-02-16 | Suspended |
5 | C | 23-01-16 | 01-02-16 | Started |
6 | D | 30-01-16 | 01-02-16 | Ended |
7 | M | 21-01-16 | 01-02-16 | Paused |
8 | W | 01-02-16 | 01-02-16 | Suspended |
9 | W | 01-02-16 | 01-02-16 | Started |
10 | A | 24-01-16 | 01-02-16 | Ended |
11 | A | 25-01-16 | 01-02-16 | Paused |
12 | B | 26-01-16 | 01-02-16 | Suspended |
13 | D | 30-01-16 | 01-02-16 | Paused |
14 | C | 31-01-16 | 01-02-16 | Suspended |
Output required is
Job Type | Count | Day1 | Day2 | Day3 | Day4 | Day5 | >Day5 |
---|---|---|---|---|---|---|---|
A | 4 | 1 | 0 | 0 | 0 | 0 | 3 |
B | 3 | 0 | 0 | 0 | 1 | 2 | |
C | 2 | 0 | 1 | 0 | 0 | 0 | 1 |
D | 2 | 0 | 0 | 3 | 0 | 0 | 0 |
M | 2 | 0 | 0 | 0 | 0 | 0 | 1 |
W | 2 | 2 | 0 | 0 | 0 | 0 | 0 |
Calculation of Day1,Day2...>Day5 is as follows
i will get ageing with the help of this formula = Today()- Job Start Date, Now if difference is 1 then it should be displayed under
"Day 1"column similarly if difference is 2 it should be displayed under "Day 2" and so on.
Count is count of Job ID
Please help me out int getting desired output.
Thanks in advance
-Priyanka
If in case you want to see 0s, instead of -, you can use 0 for missing symbol on presentation tab:
Try this:
Table:
LOAD [Job ID],
[Job Type],
[Job Start Date],
[End Date],
Status,
Dual(If(Today() - [Job Start Date] > 5, '>Day5', 'Day'& (Today() - [Job Start Date])), If(Today() - [Job Start Date] > 5, 6, (Today() - [Job Start Date]))) as DayNum
FROM
[https://community.qlik.com/thread/203909]
(html, codepage is 1252, embedded labels, table is @1);
If in case you want to see 0s, instead of -, you can use 0 for missing symbol on presentation tab:
Hi Sunny,
Thank you for instant reply.
It is working absolutely fine for me.
-Priyanka
Hi Sunny,
I am facing a small issue today.
if today's date and job start date both are same then according to above code it will display Day0, but I want to change it to Day1. Can you please tell me where to make changes.
-Priyanka
All others calculation will move back 1 day as well? or just for Today - Job start date?
only where today() and Job Start date are same
Try adding this to your code:
Table:
LOAD [Job ID],
[Job Type],
[Job Start Date],
[End Date],
Status,
Dual(If(Today() - [Job Start Date] > 5, '>Day5',
If(Today() - [Job Start Date] = 0, 'Day1', 'Day'& (Today() - [Job Start Date]))),
If(Today() - [Job Start Date] > 5, 6,
If(Today() - [Job Start Date] = 0, 1, (Today() - [Job Start Date])))) as DayNum
FROM
[https://community.qlik.com/thread/203909]
(html, codepage is 1252, embedded labels, table is @1);
Thanks Sunny
No problem at all