QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for
Did you mean:
Creator II

Head Count Analysis Calculation in Straight Table - Expression help?

Hi guys,

I have the below table I need to do in Qlikview through expressions.

The data I already have are colored in Yellow. The calculation I need to do is in blue and is dependent on green. How do I calculate blue column based on green?

For row 1,

Target To Be Met (blue) is same as Avg.Hourly Target (constant number 500).

Hourly Target Left (green) is difference between Target To Be Met (blue) and Hourly Target Met.

From row 2,

Target To Be Met (blue) is Avg.Hourly Target (constant number 500) + Hourly Target Left from previous row (400) = 900.

Hourly Target Left (green) is difference between Target To Be Met (blue) 900 and Hourly Target Met 200 = 700.

From row 3,

Target To Be Met (blue) is Avg.Hourly Target (constant number 500) + Hourly Target Left from previous row (700) = 1200.

Hourly Target Left (green) is difference between Target To Be Met (blue) 1200 and Hourly Target Met 200 = 1000.

Similarly row 4,5....

How can I formulate the blue and green with exception of row 1 or based on it, to get the desired table on top in Qlikview?

1 Solution

Accepted Solutions
Contributor

Hi Haneesh,

I've managed to create a table chart that looks like this:

having used columns coloured in yellow which I loaded in the following script:

DATA_TIME_CST, Avg. Hourly Target, Hourly Target Met, Target Per HC

1, 500, 100, 100

2, 500, 200, 100

3, 500, 200, 100

4, 500, 500, 100

5, 500, 600, 100

6, 500, 700, 100

7, 500, 500, 100

8, 500, 300, 100

9, 500, 300, 100

];

Can you please tell if this is what you expected to get?

*Field DATA_TIME_CST contains ranking numbers 1,2,3... but obviously you can store dates in it.

If this is what you are looking for please find expressions that I used in chart below:

1) Label: Avg. Hourly Target; Expression: [Avg. Hourly Target]

2) Label: Target To Be Met; Expression: =if(RowNo()=1, [Avg. Hourly Target], [Avg. Hourly Target]+above([Hourly Target Left]))

3) Label: Hourly Target Met; Expression: [Hourly Target Met]

4) Label: Hourly Target Left; Expression: =[Target To Be Met] - [Hourly Target Met]

5) Label: Target Per HC; Expression: [Target Per HC]

Worth mentioning is that, as you can see, I used references to one expression inside another one (bold and underlined text). They have to be named the same in the labels and the expressions - just in case if you are not familiarised with this.

Hope it helps

2 Replies
Contributor

Hi Haneesh,

I've managed to create a table chart that looks like this:

having used columns coloured in yellow which I loaded in the following script:

DATA_TIME_CST, Avg. Hourly Target, Hourly Target Met, Target Per HC

1, 500, 100, 100

2, 500, 200, 100

3, 500, 200, 100

4, 500, 500, 100

5, 500, 600, 100

6, 500, 700, 100

7, 500, 500, 100

8, 500, 300, 100

9, 500, 300, 100

];

Can you please tell if this is what you expected to get?

*Field DATA_TIME_CST contains ranking numbers 1,2,3... but obviously you can store dates in it.

If this is what you are looking for please find expressions that I used in chart below:

1) Label: Avg. Hourly Target; Expression: [Avg. Hourly Target]

2) Label: Target To Be Met; Expression: =if(RowNo()=1, [Avg. Hourly Target], [Avg. Hourly Target]+above([Hourly Target Left]))

3) Label: Hourly Target Met; Expression: [Hourly Target Met]

4) Label: Hourly Target Left; Expression: =[Target To Be Met] - [Hourly Target Met]

5) Label: Target Per HC; Expression: [Target Per HC]

Worth mentioning is that, as you can see, I used references to one expression inside another one (bold and underlined text). They have to be named the same in the labels and the expressions - just in case if you are not familiarised with this.

Hope it helps