Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
haneeshmarella
Creator II
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?

SS.PNG

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.


1 Solution

Accepted Solutions
konradmy
Contributor
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:

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

View solution in original post

2 Replies
konradmy
Contributor
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:

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

View solution in original post

haneeshmarella
Creator II
Creator II
Author

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