# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for
Did you mean:
Highlighted
Creator

## help required in chart expression

Hi Team,

I have few fields like below:

[Project Name]     [UT1]     [UT2]     [UT3]          [UT4]

XXX                       Y          N         [NULL]        [NULL]

YYY                        N     [NULL]     [NULL]        [NULL]

[UT] fields will can only have 3 values: Y, N, [NULL]

Now, I need to create a consolidated report based on below logic:

• If percentage of Yes out of the UTs available (ignoring [NULL] entries) is equal to 100% then Green
• If percentage of Yes out of the UTs available (ignoring [NULL] entries) is greater than 40% but less than 100% then Amber
• If percentage of Yes out of the UTs available (ignoring [NULL] entries) is less than 40% then Red

 Project Name Unit Testing Report XXX Amber YYY Red

XXX is Amber since 1 Y out of 2 entries available (ignoring Null). So 50%

YYY is Red since 0 Y out of 1 entries available (ignoring Null). So 0%

Thanks

1 Solution

Accepted Solutions
Highlighted
Master III

Hi,

see attachment.

Regards,

Antonio

5 Replies
Highlighted
Specialist II

## Re: help required in chart expression

Hi   :

Here you have a esplendid introduction into the NULL world, made by HIC NULL handling in QlikView

Hope this helps,

Joaquín

Highlighted
MVP

## Re: help required in chart expression

Try this:

Table:

If(RangeAvg(UT1, UT2, UT3, UT4) = 1, 'Green', If(RangeAvg(UT1, UT2, UT3, UT4) >= 0.40, 'Amber', 'Red')) as [Unit Testing Report];

Dual(UT1, If(UT1 = 'Y', 1, If(UT1 = 'N', 0))) as UT1,

Dual(UT2, If(UT2 = 'Y', 1, If(UT2 = 'N', 0))) as UT2,

Dual(UT3, If(UT3 = 'Y', 1, If(UT3 = 'N', 0))) as UT3,

Dual(UT4, If(UT4 = 'Y', 1, If(UT4 = 'N', 0))) as UT4;

Project Name, UT1, UT2, UT3, UT4

XXX, Y, N

YYY, N

];

Output:

Highlighted
Creator

Hi sunindia,

Highlighted
Master III

Hi,

see attachment.

Regards,

Antonio

Highlighted
MVP

## Re: help required in chart expression

Try this expression with Project Name as dimension

=If(RangeAvg(If(UT1 = 'Y', 1, If(UT1 = 'N', 0)),

If(UT2 = 'Y', 1, If(UT2 = 'N', 0)),

If(UT3 = 'Y', 1, If(UT3 = 'N', 0)),

If(UT4 = 'Y', 1, If(UT4 = 'N', 0))) = 1, 'Green',

If(RangeAvg(If(UT1 = 'Y', 1, If(UT1 = 'N', 0)),

If(UT2 = 'Y', 1, If(UT2 = 'N', 0)),

If(UT3 = 'Y', 1, If(UT3 = 'N', 0)),

If(UT4 = 'Y', 1, If(UT4 = 'N', 0))) >= 0.40, 'Amber', 'Red'))