# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

-Priyanka

Tags (1)
1 Solution

Accepted Solutions
MVP

## Re: Calculative dimensions

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

9 Replies
MVP

## Re: Calculative dimensions

Try this:

Table:

[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

(html, codepage is 1252, embedded labels, table is @1);

MVP

## Re: Calculative dimensions

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

Not applicable

## Re: Calculative dimensions

Hi Sunny,

It is working absolutely fine for me.

-Priyanka

Not applicable

## Re: Calculative dimensions

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

MVP

## Re: Calculative dimensions

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

Not applicable

## Re: Calculative dimensions

only where today() and Job Start date are same

MVP

## Re: Calculative dimensions

Table:

[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

(html, codepage is 1252, embedded labels, table is @1);

Not applicable

Thanks Sunny

MVP

## Re: Calculative dimensions

No problem at all