Showing Maximum on Stacked Chart with Drill down Group

[shane_spencer]

I'm collecting perfmon metrics for 2 processes, qvb and qvs (on two different servers - I've put logic in to only show data when a server is selected). This date is collected at a five minute intervals. I'm building a dashboard around this date with a  chart that shows Date, Hour or Minute in a drill down along the X-axis.

I want to show the Maximum sum of these two values at any point in the day.

What I've got so far is inaccurate unless I'm at Minute display in so far as it finds the maximum qvs in the entire hour or day then finds the maximum qvb and adds them together even if these 2 values don't occur at the same time.

What I was is to add qvb to qvs then show the maximum of this sum in the Hour and Date view. Ideally I want to display this as a stacked chart so I can see the value of qvb and qvs as two distinct values rather than a single value. If this is not possible an acceptable compromise would be to show a single max value on the Date, and Hour view but separate stacked values on the Minute view.

Is this possible and if so how? Attached is the work I've got so far with some example data.

[shane_spencer]

Anyone got any suggestions?

[shane_spencer · Accepted solution]

I have found a work-around. Partly using AGGR, for which Ad Bellerby's excellent Blog "http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/07/aggr" was very helpful. And also using some previous advice from Gysbert Wassenaar about how to use GetCurrentField in conjunction with a Drill down group - Chart: Sum of values of mulitiple entities then Avg / Max / Min over Time

When the chart is in Minute view it will show the stacked values of QVS alongside the the sum of QVB.

i.e.

=if(GetCurrentField([Date_drill2])='Minute',if (GetFieldSelections(Server)='GBW04938',Max(Aggr(Sum ({$<Main_metric={'Process(qvb*)'},sub_Metric={'Private Bytes'} >}value),Minute,Hour,Date)/1024/1024)))

=if(GetCurrentField([Date_drill2])='Minute',if (GetFieldSelections(Server)='GBW04938',Max ({$<Main_metric={'Process(qvs)'},sub_Metric={'Private Bytes'} >}value)/1024/1024))

At Hour and Date view (not Minute) it will calculated the sum of all the QVS and QVB process (i.e. qvs, qvb, qvb#1, qvb#2 etc) at any given Minute the display the Maximum value (for that Hour or Date respectively).

i.e.

=if(GetCurrentField([Date_drill2])<>'Minute',if (GetFieldSelections(Server)='GBW04938',Max(Aggr(Sum ({$<Main_metric={'Process(qvb*)','Process(qvs*)'},sub_Metric={'Private Bytes'} >}value),Minute,Hour,Date)/1024/1024)))

I have attached the solution in hope this might be of use to others.