Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
sayadutt
Creator
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%


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



Thanks

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Hi,

see attachment.

Regards,

Antonio

View solution in original post

5 Replies
Joaquin_Lazaro
Partner - Specialist II
Partner - Specialist II

Hi   :

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

Hope this helps,

Joaquín

sunny_talwar

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:


Capture.PNG

sayadutt
Creator
Creator
Author

Hi sunindia,

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

antoniotiman
Master III
Master III

Hi,

see attachment.

Regards,

Antonio

sunny_talwar

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