Urgent - creating HeatMap based on conditions

[sayadutt]

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

[JonnyPoole]

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

[sayadutt]

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

[JonnyPoole]

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.

[sayadutt]

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

[sayadutt]

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:

[JonnyPoole]

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

[sayadutt]

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

[JonnyPoole]

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