Calculative dimensions

[#1]

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

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

If in case you want to see 0s, instead of -, you can use 0 for missing symbol on presentation tab:

[#4]

Hi Sunny,

Thank you for instant reply.

It is working absolutely fine for me.

-Priyanka

[#5]

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

[sunny_talwar]

All others calculation will move back 1 day as well? or just for Today - Job start date?

[#7]

only where today() and Job Start date are same

[sunny_talwar]

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

[#9]

Thanks Sunny

[sunny_talwar]

No problem at all