topic Re: Re: Re: Urgent - creating HeatMap based on conditions in QlikView

Urgent - creating HeatMap based on conditions

sayadutt — Wed, 03 Sep 2014 18:58:56 GMT

Hi,

Below are my 2 tables.

Main: consists of Release information

Weekwise: weekwise details for each above releases

So, 1 Release can have multiple records in weekwise table.

Main:

LOAD * inline [

Area,Application,ReleaseNo,StartDate,EndDate

AA, ABC, 1.1, 08/19/2014, 09/09/2014

AA, XYZ, 2.1, 07/29/2014, 08/09/2014

BB, EFH, 10.1, 08/19/2014, 09/30/2014

];

Weekwise:

LOAD * inline [

Area,Application,ReleaseNo,WeekEndDate,RAGStatus

AA, ABC, 1.1, 08/22/2014, Green

AA, ABC, 1.1, 08/29/2014, Amber

AA, ABC, 1.1, 09/05/2014, Green

AA, XYZ, 2.1, 09/05/2014, Green

BB, EFH, 10.1, 08/22/2014, Amber

BB, EFH, 10.1, 08/29/2014, Amber

];

My requirement is to create a Heatmap based on Area and show the RAG status for the last (recent) week.

From above example:

Release 1.1 has 3 entries in Weekwise table, but I need to show the latest week status : 09/05/2014 which is Green.

Heatmap should have only three colors: Green, Amber, Red

Please help.

Thanks

Re: Urgent - creating HeatMap based on conditions

JonnyPoole — Wed, 03 Sep 2014 21:17:45 GMT

Here is a sample. Please elaborate on the kind of visualization you may be interested in.

Re: Urgent - creating HeatMap based on conditions

sayadutt — Wed, 03 Sep 2014 23:42:09 GMT

Hi Jonathan,

Thanks for your reply.

For visualization:

I am looking for a Heatmap chart having Area as dimension and showing count of Release Numbers along with their current(latest) RAG colors in expression.

Please suggest.

Thanks

Re: Re: Urgent - creating HeatMap based on conditions

JonnyPoole — Thu, 04 Sep 2014 01:22:19 GMT

I see it . I had to change my firstsortedvalue to pick the last weekenddate (-weekenddate). I also had to ensure it was carried out within the application dimension

changed:

FirstSortedValue( RAGStatus,WeekEndDate)

to:

FirstSortedValue( total <Application> RAGStatus,-WeekEndDate)

try the attached app.

For a visualization , can you mock up a diagram of what you are asking. I'm not clear about that. even a handwritten chart will probably do.

Re: Urgent - creating HeatMap based on conditions

sayadutt — Thu, 04 Sep 2014 06:24:26 GMT

Hi Jonathan,

Your above script is actually what I was looking for.

Instead of heatmap, can you please help how can I show a simple table with count of latest RAG with percentage.

For eg:

Thanks

Re: Re: Urgent - creating HeatMap based on conditions

sayadutt — Mon, 15 Sep 2014 19:31:11 GMT

Hi Jonathan,

Can you please help me here. I need this solution badly.

Instead of heatmap, can you please help how can I show a simple table with count of latest RAG with percentage.

For eg:

Re: Re: Re: Urgent - creating HeatMap based on conditions

JonnyPoole — Mon, 15 Sep 2014 20:50:48 GMT

I added a list box example and a chart example of what you are asking for.

Re: Urgent - creating HeatMap based on conditions

sayadutt — Tue, 16 Sep 2014 08:12:51 GMT

Hi Jonathan,

In your attached sample, we need the LATEST RAG status(latest week) and not all the week status.

so, below are the expected result from the sample:

So, the RAG status should show like this (and not the one present in the sample)

Green	1	33.33%
Amber	1	33.33%
Red	1	33.33%

Please help.

Thanks

Re: Re: Urgent - creating HeatMap based on conditions

JonnyPoole — Tue, 16 Sep 2014 20:23:10 GMT

Okay. The list box is view is easier if you use a calculated field for the list box:

In the list box, change the source to 'edit' and then enter:

=aggr( FirstSortedValue( RAGStatus,-WeekEndDate), Application&ARea)

Then your first expression is: sum(1)

And the 2nd expression is: num(sum(1) / count( distinct TOTAL area&Application) , '#,###.0%')