topic Re: Aggr function for a specific time period in QlikView

Aggr function for a specific time period

shinnickr — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Aggr function for a specific time period

sunny_talwar — Thu, 28 Dec 2017 20:55:30 GMT

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

Re: Aggr function for a specific time period

shinnickr — Fri, 29 Dec 2017 12:58:39 GMT

Woohoo! That's it. Thanks Sunny!

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

Re: Aggr function for a specific time period

sunny_talwar — Fri, 29 Dec 2017 13:00:39 GMT

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

Re: Aggr function for a specific time period

shinnickr — Fri, 29 Dec 2017 13:02:01 GMT

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

Re: Aggr function for a specific time period

sunny_talwar — Fri, 29 Dec 2017 13:09:48 GMT

My pleasure

Re: Aggr function for a specific time period

shinnickr — Fri, 29 Dec 2017 13:32:05 GMT

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.

Re: Aggr function for a specific time period

sunny_talwar — Fri, 29 Dec 2017 13:35:45 GMT

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

Re: Aggr function for a specific time period

shinnickr — Fri, 29 Dec 2017 13:44:36 GMT

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!!

Re: Aggr function for a specific time period

sunny_talwar — Fri, 29 Dec 2017 13:46:14 GMT

I am glad it worked