Aggr function for a specific time period

[shinnickr]

Right now I am trying to kind of create my own heat-map in Qlikview.  I have it mostly working, but I need some help with some set analysis and aggregation of it.

I am using the colormix function to set a upper and lower limit and using some set analysis combined with the aggr() function.

max(aggr(Sum({<[Discharge Date ID]= P({$}[Reference Date ID])>}[IP Discharge Flg Cnt]), [Discharge Month]))

I am summing Inpatient Discharges and getting the max discharges in all the months we have data for.  The problem is that the time frame I want to use for my heat map is 12 month fiscal calendar, and people are able to choose the fiscal year.  So when I use the colormix function to set a sliding scale for the heatmap, the upper limit (or max()), is the highest count of discharges in every month that we have data for.  What I want is for the [Discharge Month] part of the aggregation to be the same set analysis that is in the Sum() part.  That way the limits for my colormix will be for the shown data, rather than all of it.  So my heatmap will be dynamic based on the date selection.

Is this possible at all?

Any help would be appreciated, and if you need any further clarification feel free to ask.

Thank you!

[sunny_talwar · Accepted solution]

Is this what you are looking for?

Max({<[Discharge Date ID]= P({$}[Reference Date ID])>}aggr(Sum({<[Discharge Date ID]= P({$}[Reference Date ID])>}[IP Discharge Flg Cnt]), [Discharge Month]))

[shinnickr]

Woohoo!  That's it.  Thanks Sunny!

So the correct syntax for applying set analysis to aggregation is to put it before the aggr?

[sunny_talwar]

You can see it as that way, but I see it that functions such as Sum/Max/Min/Avg/Median/Concat... etc are the ones that take set analysis... so I basically added it to the Max() function

[shinnickr]

Ahh okay that definitely makes sense.  Thank you for helping me understand a bit more!

[sunny_talwar]

My pleasure

[shinnickr]

Hey Sunny,

If I could just pick your brain again, without having to create another post...

I have the heat map working fairly well, but I'm looking to make it a little better.  I have a cycle group in the dimensions that cycles between multiple ways of looking at the data.  The formula I'm using for the colormix for the heat map is:

colormix1(Sum({<[Discharge Date ID]= P({$}[Reference Date ID])>}[IP Discharge Flg Cnt]) / $(=Max({<[Discharge Date ID]= P({$}[Reference Date ID])>}aggr(Sum({<[Discharge Date ID]= P({$}[Reference Date ID])>}[IP Discharge Flg Cnt]), [Discharge Month]))), RGB(191, 223, 255), RGB(0, 45, 90))

What I'd like to do is somehow incorporate GetFieldSelection(Comparison Group) into it somewhere so that it further aggregates over the current dimension.  I've tried a few different ways, but nothing seemed to work.  That way the colors will be more dynamic.  Because right now the overall max of all the data in the current aggregation is 4,088.  But when I have a dimension with a lot of rows, all the numbers are very small.  So all the colors are on the lighter side, because none of them get close to the overall max.

[sunny_talwar]

Something like this

colormix1(Sum({<[Discharge Date ID]= P({$}[Reference Date ID])>}[IP Discharge Flg Cnt]) / $(=Max({<[Discharge Date ID]= P({$}[Reference Date ID])>}Aggr(Sum({<[Discharge Date ID]= P({$}[Reference Date ID])>}[IP Discharge Flg Cnt]),

$(='[' & GetCurrentField([Group Name]) & ']')

))), RGB(191, 223, 255), RGB(0, 45, 90))

[shinnickr]

Gosh, Sunny.  You're so good.  Thank you!!  That is exactly what I needed.

It's lookin' much better now, with your help.  Thank you again!!

[sunny_talwar]

I am glad it worked