topic Head Count Analysis Calculation in Straight Table - Expression help? in QlikView

Head Count Analysis Calculation in Straight Table - Expression help?

haneeshmarella — Wed, 25 Nov 2020 16:16:04 GMT

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?

Your guidance is highly appreciated.

Re: Head Count Analysis Calculation in Straight Table - Expression help?

Anonymous — Sat, 21 Jul 2018 09:54:56 GMT

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:

LOAD * INLINE [

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

Konrad

Re: Head Count Analysis Calculation in Straight Table - Expression help?

haneeshmarella — Mon, 23 Jul 2018 15:01:21 GMT

Perfect! I actually tried doing it before I posted here, but the expression when I used 'Label References' showed 'Error in Expression', and I did not apply it to check if it works. Even after you providing the solution shows 'Error in Expression' but when I apply it, it does work!

Thank you Konrad