topic Re: help required in chart expression in QlikView

help required in chart expression

sayadutt — Wed, 27 May 2015 12:57:19 GMT

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%

Can you please help me in creating this above straight chart.

Thanks

Re: help required in chart expression

Joaquin_Lazaro — Wed, 27 May 2015 13:13:47 GMT

Hi :

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

Hope this helps,

Joaquín

Re: help required in chart expression

sunny_talwar — Wed, 27 May 2015 13:31:11 GMT

Try this:

Table:

LOAD *,

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

Load [Project Name],

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;

LOAD * INLINE [

Project Name, UT1, UT2, UT3, UT4

XXX, Y, N

YYY, N

];

Output:

Re: help required in chart expression

sayadutt — Wed, 27 May 2015 13:39:55 GMT

Hi sunindia,

Can we please have this in chart exp and not while loading.

Re: help required in chart expression

antoniotiman — Wed, 27 May 2015 13:43:30 GMT

Hi,

see attachment.

Regards,

Antonio

Re: help required in chart expression

sunny_talwar — Wed, 27 May 2015 13:44:56 GMT

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'))