Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculative dimensions

Hi All,

I have a excel data source containing data in below format.

   

Job IDJob TypeJob Start DateEnd Date Status
1A23-01-1601-02-16Started
2B25-01-1601-02-16Ended
3B28-01-1601-02-16Paused
4A01-02-1601-02-16Suspended
5C23-01-1601-02-16Started
6D30-01-1601-02-16Ended
7M21-01-1601-02-16Paused
8W01-02-1601-02-16Suspended
9W01-02-1601-02-16Started
10A24-01-1601-02-16Ended
11A25-01-1601-02-16Paused
12B26-01-1601-02-16Suspended
13D30-01-1601-02-16Paused
14C31-01-1601-02-16Suspended

Output required is

 

Job TypeCountDay1Day2Day3Day4Day5>Day5
A4100003
B3000 12
C2010001
D2003000
M2000001
W2200000

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

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

View solution in original post

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


Capture.PNG

sunny_talwar

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

Capture.PNG

Not applicable
Author

Hi Sunny,

Thank you for instant reply.

It is working absolutely fine for me.

-Priyanka

Not applicable
Author

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?

Not applicable
Author

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

Not applicable
Author

Thanks Sunny

sunny_talwar

No problem at all