topic Re: Calculative dimensions in QlikView

Calculative dimensions

Tue, 02 Feb 2016 17:20:29 GMT

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

Re: Calculative dimensions

sunny_talwar — Tue, 02 Feb 2016 17:28:45 GMT

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

Re: Calculative dimensions

sunny_talwar — Tue, 02 Feb 2016 17:30:53 GMT

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

Re: Calculative dimensions

Tue, 02 Feb 2016 17:38:48 GMT

Hi Sunny,

Thank you for instant reply.

It is working absolutely fine for me.

-Priyanka

Re: Calculative dimensions

Wed, 03 Feb 2016 09:54:55 GMT

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

Re: Calculative dimensions

sunny_talwar — Wed, 03 Feb 2016 09:57:48 GMT

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

Re: Calculative dimensions

Wed, 03 Feb 2016 10:00:01 GMT

only where today() and Job Start date are same

Re: Calculative dimensions

sunny_talwar — Wed, 03 Feb 2016 10:04:46 GMT

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

Re: Calculative dimensions

Wed, 03 Feb 2016 10:16:16 GMT

Thanks Sunny

Re: Calculative dimensions

sunny_talwar — Wed, 03 Feb 2016 10:17:07 GMT

No problem at all